從MYSQL資料庫的另一張表中提取數據，對現有列進行更改

Question

我有一個MYSQL資料庫（在PHPMyAdmin中），其中有兩個表格users和posts。兩個表都有一個username列。我想修改posts表中的username列，使其從users表中提取使用者名稱數據，即posts表中的資料會自動從users表中更新，並在對users表進行任何更新時引用它。

我最初認為可以使用外鍵來實現這個功能，但如果我理解正確，外鍵只與父表中的主鍵相關聯，是嗎？

我收到一個錯誤訊息，說以下語法不正確，儘管它沒有給出任何提示/解決方案：

ALTER TABLE posts
MODIFY COLUMN username VARCHAR(55) NOT NULL
REFERENCES users(username) ON UPDATE CASCADE

如何修改現有列，使其引用/使用資料庫中不同表中的列的資料？

兩個表中的username列具有相同的類型、大小和屬性，即VARCHAR(55) NOT NULL，並且使用的是innoDB儲存引擎。

Answer 1

外鍵是一種資料完整性檢查，僅此而已。它確保子表中的欄位包含父表中引用欄位中出現的值。就是這樣^*。

你不能使用外鍵將一張表的資料合併到另一張表中。然而，JOIN語句剛好可以滿足你的需求，並且與外鍵一起使用可以確保每個貼文都有一個有效的使用者名稱來識別正確的使用者資料。

以這個例子為例：

用戶資料

CREATE TABLE `userdata` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `displayName` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

帖子

CREATE TABLE `posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_posts_userdata_idx` (`username`),
  CONSTRAINT `fk_posts_userdata` FOREIGN KEY (`username`) REFERENCES `userdata` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

我們可以使用以下查詢將userdata表格與posts表格進行JOIN：

select 
    `posts`.`id` AS `postId`,
    `posts`.`username` AS `username`,
    `posts`.`message` AS `message`,
    `userdata`.`id` AS `userId`,
    `userdata`.`displayName` AS `displayName`,
    `userdata`.`email` AS `email` 
from (`posts` 
         join `userdata` 
         on(`userdata`.`username` = `posts`.`username`));

你也可以進一步建立一個基於這個查詢的VIEW來傳回資料：

CREATE VIEW `posts_users` AS 
  select 
    `posts`.`id` AS `postId`,
    `posts`.`username` AS `username`,
    `posts`.`message` AS `message`,
    `userdata`.`id` AS `userId`,
    `userdata`.`displayName` AS `displayName`,
    `userdata`.`email` AS `email` 
  from (`posts` 
         join `userdata` 
         on(`userdata`.`username` = `posts`.`username`));

使用SELECT語句查詢檢視：

select * from posts_users order by postId

示範：https://www.db-fiddle.com/f/tbBXvthBtwH7CKu1yjzPjQ/0

^* 外鍵也允許更新和刪除父表時級聯到子表，但這超出了本文的範圍。