MySQL零散筆記–外鍵

References: 《淺談MySQL外鍵》《mysql建立外部索引鍵關聯》

MySQL中“鍵”和“索引”的定義相同， 所以外鍵和主鍵一樣也是索引的一種。不同的是MySQL會自動為所有表的主鍵進行索引，但是外鍵欄位必須由使用者進行明確的索引。//查看Mysql手冊發現從MySQL 4.1.2開始會自動建立這個INDEX

建立外鍵的執行個體代碼：員工和工資表:

/*

建立員工表

*/

create table employees ( 

id int(5) not null auto_increment , 

name varchar(8) not null, 

primary key (id)

) 

type＝innodb;

/*

建立工資表

*/

create table payroll( 

id int(5) not null, 

emp_id int(5) not null, 

name varchar(8) not null, 

payroll float(4,2) not null, 

primary key(id), 

index emp_id (emp_id),

foreign key (emp_id) references employees (id)

)

type = innodb;

參照完整性(Referentialintegrity)通常通過外鍵(foreign key)的使用而被廣泛應用，在MySQL中通過新的InnoDB列表引擎支援。為了建立兩個MySQL表之間的一個外鍵關係，必須滿足以下三種情況：

* 兩個表必須是InnoDB表類型。  

* 使用在外鍵關係的域必須為索引型(Index)。  

* 使用在外鍵關係的域必須與資料類型相似。

怪不得我試著建立帶外鍵的表的時候出錯呢，原來是之前的表不是InnoDB類型，果斷該表表的類型：

ALTER TABLE table-name TYPE=INNODB;

居然有Warning。 show warnings之後發現原來是 “The syntax 'TYPE=storage_engine' is deprecated and will be removed in MySQL 6.0. Please use 'ENGINE=storage_engine' instead”  TYPE已經OUT啦~以後要用Engine了嗯

關於表類型(type/engine)的介紹，可以在這裡找到：

《淺談MySQL表類型》，《MySQL engine/type類型InnoDB/MYISAM/MERGE/BDB/HEAP的區別》

注意事項：

• 關係中的所有表必須是innoDB表，在非InnoDB表中，MySQL將會忽略FOREIGN KEY…REFERENCES修飾符。
• 用於外鍵關係的欄位必須在所有的參照表中進行明確地索引，InnoDB不能自動地建立索引。
• 在外鍵關係中，欄位的資料類型必須相似，這對於大小和符號都必須匹配的整數類型尤其重要。
• 即使表存在外鍵約束，MySQL還允許我們刪除表，並且不會產生錯誤(即使這樣做可能會破壞更早建立的外鍵)

 

刪除外鍵方法:

ALTER TABLE table-name DROP FOREIGN KEY key-id;

這裡有一個概念，這個外鍵的id是啥玩意？我們可以通過SHOW CREATE TABLＥ 命令來獲得key-id的值。

/*

顯示建表結構語句，key-id為payroll_ibfk_1

*/

show create table payroll \G

/*

*************************** 1. row ***************************

       Table: payroll

Create Table: CREATE TABLE `payroll` (

  `id` int(5) NOT NULL,

  `emp_id` int(5) NOT NULL,

  `name` varchar(8) NOT NULL,

  `payroll` float(4,2) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `emp_id` (`emp_id`),

  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*/

 

自動鍵更新和刪除：

MySQL可能通過向FOREIGN KEY…REFERENCES 修飾符添加一個ON DELETE或ON UPDATE子句簡化任務，它告訴了資料庫在這種情況如何處理孤立任務。

關鍵字	含義
CASCADE	刪除包含與已刪除索引值有參照關係的所有記錄.就是刪除外鍵記錄
SET NULL	修改包含與已刪除索引值有參照關係的所有記錄，使用NULL值替換(只能用於已標記為NOT NULL的欄位)
RESTRICT	拒絕刪除要求，直到使用刪除索引值的輔助表被手工刪除，並且沒有參照時(這是預設設定，也是最安全的設定)
NO ACTION	啥也不做

請注意，通過 ON UPDATE 和ON DELETE規則，設定ＭｙＳＱＬ能夠實現自動操作時，如果鍵的關係沒有設定好，可能會導致嚴重的資料破壞。