[SQL] REFERENCES 外來鍵的用法(含範例與mysql)

Posted on by Bocky

下方演示: MySQL
GUI: tabPlus

在學習SQL時一定會學到外來鍵，外來鍵的目的是確定資料的參考完整性(referential integrity)，簡單來說，就是只有被准許的資料值才會被存入資料庫內

下方以一個生活例子來舉例:
1個頻道(電視台)會依據不同時間有不同節目，當然沒有頻道就不會有節目撥出，也就是一對多。如下圖:

頻道:

CREATE TABLE `channel` (
  `id` int PRIMARY KEY,
  `channel_name` varchar(255) NOT NULL
);

節目:

CREATE TABLE `programme` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `channel_id` int,
  `programme_name` varchar(255),
  `start_time` datetime DEFAULT (now()),
  `end_time` datetime DEFAULT (now()),
  FOREIGN KEY(channel_id) REFERENCES channel(id)
);

加入資料1:

INSERT INTO `channel` (`id`, `channel_name`) VALUES
(7, '恩恩新聞'),
(8, '探索世界'),
(9, '東西購物');

加入資料2:

INSERT INTO `programme` (`channel_id`, `programme_name`, `start_time`, `end_time`) VALUES
(7, '3點新聞', '2022-03-01 15:00:00', '2022-03-01 16:00:00'),
(7, '4點新聞', '2022-03-01 16:00:00', '2022-03-01 17:00:00'),
(7, '5點新聞', '2022-03-01 17:00:00', '2022-03-01 18:00:00'),
(8, '說書人', '2022-03-01 15:00:00', '2022-03-01 16:00:00'),
(8, '科學時間', '2022-03-01 16:00:00', '2022-03-01 17:00:00'),
(8, '海底裡', '2022-03-01 17:00:00', '2022-03-01 18:00:00'),
(9, '3點購物時間', '2022-03-01 15:00:00', '2022-03-01 16:00:00'),
(9, '4點購物時間', '2022-03-01 16:00:00', '2022-03-01 17:00:00'),
(9, '5點購物時間', '2022-03-01 17:00:00', '2022-03-01 18:00:00'),

嘗試當加入一筆沒有在channel內的id

INSERT INTO `programme` (`channel_id`, `programme_name`, `start_time`, `end_time`) VALUES
(10, '一起出去玩', '2022-03-01 17:00:00', '2022-03-01 18:00:00');

錯誤訊息如下:

Cannot add or update a child row: a foreign key constraint fails (`general`.`programme`, CONSTRAINT `programme_ibfk_1` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`))

補充

當表已經建立完畢後，設置外來鍵:

ALTER TABLE `programme` ADD FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`);

可參考:fooish

發佈留言