MySQL foreign key(外鍵) 說明與實例

一,什么是foreign key，及其完整性 個人覺得，foreign key就是表與表之間的某種約定的關系，由于這種關系的存在，我們能夠讓表與表之間的數據，更加的完整，關連性更強。關于完整性，關連性我舉個例子，大家就會明白了。 有二張表，一張是用戶表，一張是訂單表： 1，如果我刪除了用戶表里的用戶，那么訂單表里面根這個用戶有關的數據，就成了無頭數據了，不完整了。 2，如果我在訂單表里面，隨便插入了一條數據，這個訂單在用戶表里面，沒有與之對應的用戶。這樣數據也不完整了。 如果有外鍵的話，就方便多了，可以不讓用戶刪除數據，或者刪除用戶的話，通過外鍵同樣刪除訂單表里面的數據，這樣也能讓數據完整。 4，mysql外鍵的名子在數據庫內要是唯一的 二，創建foreign key的語法規則 [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …) REFERENCES tbl_name (index_col_name, …) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] 三，外鍵維護數據完整性的5種方式 1，CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在兩個表之間，你不應定義若干在父表或子表中的同一列采取動作的ON UPDATE CASCADE子句。 2，SET NULL: 從父表刪除或更新行，并設置子表中的外鍵列為NULL。如果外鍵列沒有指定NOT NULL限定詞，這就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。 3，NO ACTION: 在ANSI SQL-92標準中，NO ACTION意味這不采取動作，就是如果有一個相關的外鍵值在被參考的表里，刪除或更新主要鍵值的企圖不被允許進行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒絕對父表的刪除或更新操作。 4，RESTRICT: 拒絕對父表的刪除或更新操作。NO ACTION和RESTRICT都一樣，刪除ON DELETE或ON UPDATE子句。（一些數據庫系統有延期檢查，并且NO ACTION是一個延期檢查。在MySQL中，外鍵約束是被立即檢查的，所以NO ACTION和RESTRICT是同樣的）。 5，SET DEFAULT: 這個動作被解析程序識別，但InnoDB拒絕包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定義。

四、不建議用自動編號字段作為外鍵關系約束的字段。

不過，你要堅持這么用，當然也可以。

先追加主記錄后，用select @@identity獲取新追加的主表的id1，然后在追加子表時，用這個值作為子表id1的值

五、主鍵和外鍵是相對于整個數據庫來說的，總體來說就是要實現數據庫的實體完整性、參照完整性等完整性的約束。并滿足數據庫規范化的要求 1NF、2NF… 對于一個表定義了一個主鍵，對于該表可以為通過該鍵唯一地表示表中的每一記錄。外鍵是相對于表中的一個列給它的一個約束，一般是另一個表中的主鍵，該列的值必須在另一個表中出現。 由此可見，外鍵表示了兩個關系之間的聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。外鍵又稱作外關鍵字。 外鍵的作用： 保持數據一致性，完整性，主要目的是控制存儲在外鍵表中的數據。 使兩張表形成關聯，外鍵只能引用外表中的列的值！ 恩, 好象不缺少一些關系數據庫的基礎知識. 簡單的說, 在關系型數據庫中的每一個表都具備這樣的特點, 在同一個表中沒有完全一致的兩行數據, 當一行數據可以有少數幾個屬性確定的話,這幾個屬性就可以成為主鍵, 設立了表的主鍵之后,數據庫系統會為主鍵的列建立索引和制約, 利用主鍵進行檢索的速度是最快的. 如果有兩個表AB, 他們之間存在著某種對應關系,這種關系可以是A中一行記錄對應于B中的一行記錄,也可以是A的一行記錄對應于B的多行記錄,還可以是A的多行對應于B的多行,在關系型數據庫中, 這種對應關系也要做成關系表,下面就一對一, 一對多, 多對多幾種情況來說明一下關系表 一對一 表A(學號, 姓名) 表B(學號, 學習成績) 表A是一份學生名單, 學號是主鍵 表B是一份成績單, 學號也是主鍵,這樣兩個表就是一對一的關系, 反映出這一關系的是學號, 如果把表A做為主表的話, 表B的學號就是外鍵了

一對多 表A(班級, 人數) 表B(學號, 姓名) 表A是一份班級列表, 表B是一分學生名單,每個學生都屬于某一個班級, 而一個班級可以有很多學生 為了表示這樣的關系, 可以做一個關系表C(學號,班級) 其中,學號是主鍵, 這就保證了一對多的關系, 而學號和班級, 都是外鍵, 由于B,C

多對多 表A(課程, 學分) 表B(學號, 姓名) 表A是一份課程表 表B是份名單, 一個學生可以選多門課程,一門課程中也可以有很多學生, 為了反映這樣的關系建立表C(課程,學號)其中課程和學號都是主鍵,這就保證了多對多的關系, 同時他們也是外鍵

作為外鍵的屬性反映了主表中的某些屬性, 因此其類型要和主表的對應屬性一致才行 建立了外鍵之后會產生一致性的制約, 比如說, 在一對多的例子中, 不允許在關系表中出現主表中沒有的學號或者是班級, 另外,對外鍵的更新和刪除, 也就有了下面兩種制約 - 級聯, 當刪除了主表的主鍵之后, 同時也刪除關系表中的外鍵, 比如,刪除了一對多的例子中刪除了班級, 系統會自動刪除關系表C中含有該班級的所有記錄 - 制約, 只有當關系表中沒有與該主鍵對應的外鍵時才允許刪除該主鍵,再比如, 在一對多的例子中, 想刪除某一班級的話,就必須先刪除關系表C中所有含該班級的記錄

外鍵的設立可以保證數據的對應關系避免因為錯誤操作而遭到破壞,同時, 外鍵也可以提示系統事先建立索引和結合操作的執行計劃,從而提高結合計算的效率 　　例如： 　　a b 兩個表 　　a表中存有客戶號，客戶名稱 　　b表中存有每訂個客戶的單 　　有了外鍵后 　　你只能在確信b 表中沒有客戶x的訂單后，才可以在a表中刪除客戶x 　　建立外鍵的前提： 本表的列必須與外鍵類型相同(外鍵必須是外表主鍵)。 　　指定主鍵關鍵字： foreign key(列名) 　　引用外鍵關鍵字： references <外鍵表名>(外鍵列名)（被參考的表） 　　事件觸發限制: on delete和on update ,可設參數cascade(跟隨外鍵改動), restrict(限制外表中的外鍵改動),set Null(設空值）,set Default（設默認值）,[默認]no action 　　例如： 　　outTable表 主鍵 id類型 int 　　創建含有外鍵的表： 　　create table temp( 　　id int, 　　name char(20), 　　foreign key(id) references outTable(id) on delete cascade on update cascade); 　　說明：把id列 設為外鍵 參照外表outTable的id列 當外鍵的值刪除 本表中對應的列篩除 當外鍵的值改變 本表中對應的列值改變。 　　建鍵幾個原則: 　　1、 為關聯字段創建外鍵。 　　2、 所有的鍵都必須唯一。 　　3、避免使用復合鍵。 4、外鍵總是關聯唯一的鍵字段。 在關系中所有表中具有相同含義的字段作為公共部分來連接不同表中的記錄。外鍵可以是一對一的，一個表的記錄只能與另一個表的一條記錄連接，或者是一 對多的，一個表的記錄與另一個表的多條記錄連接。 MySQL中“鍵”和“索引”的定義相同， 所以外鍵和主鍵一樣也是索引的一種。不同的是MySQL會自動為所有表的主鍵進行索引，但是外鍵字段必須由用戶進行明確的索引。這和一些封建思想比較沉重的家庭是一樣的，外來的孩子(兒媳婦，倒插門女婿)一般都是不受重視的。 表間一對一關系示例： 有兩張表，第一張表是記錄公司有多少人，都有誰，也就是員工編號及員工姓名這些基本表。另一張表記錄每個月發給用戶多少工資，所謂工資表是也。 但是工資表里面不能以員工姓名為主鍵，同樣要通過員工id，因為員工的姓名是可能重復的啊。部門經理叫張三，小弟也叫張三，那這倆張三的工資能一樣嗎？并且員工表里面的每個人都有工資，否則誰也不給你干活，且一個人只能有一份工資，否則老板也不同意了。所以員工表和工資表是通過員工id進行關聯的一 對一關系。 /建立員工表/ 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; 參照完整性： 當外鍵與另一個表的字段有關系，而且這種關系是惟一時，這個系統就稱為處于參照完整性的狀態。也就是說，如果一個字段在所有的表中只出現一次，而且每個表的這個字段的變化都會影響其他表，這就是存在參照完整性。 術語理解上可能不太方便，其實就是說要在有外鍵的表中保持所有數據的一致性。比如說“張三”離職了，在員工表里面肯定沒有這個人了，可是如果在工資表里面還存在這個孩子，那么老大就會很生氣的。 MySQL的外鍵只能在InnoDB表中使用： MySQL對此一直持觀望態度，它允許使用外鍵，但是為了完整性檢驗的目的，在除了InnoDB表類型之外的所有表類型中都忽略了這個功能。這可能有些怪異，實際上卻非常正常：對于數據庫的所有外鍵的每次插入、更新和刪除后，進行完整性檢查是一個耗費時間和資源的過程，它可能影響性能，特別是當處理 復雜的或者是纏繞的連接樹時。因而，用戶可以在表的基礎上，選擇適合于特定需求的最好結合。。 所以，如果需要更好的性能，并且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根據參照完整性來建立表并且希望在此基 礎上保持良好的性能，最好選擇表結構為innoDB類型。 MySQL創建外鍵語法： 創建外鍵的語法是這樣的：FOREIGN KEY (當前表的字段名)… REFERENCES參照表 (參照表的字段名) foreign key (emp_id) references employees (id);的意思就是說當前表的emp_id字段是以employees的id字段為外鍵的。 注意事項： 關系中的所有表必須是innoDB表，在非InnoDB表中，MySQL將會忽略FOREIGN KEY…REFERENCES修飾符。 用于外鍵關系的字段必須在所有的參照表中進行明確地索引，InnoDB不能自動地創建索引。 在外鍵關系中，字段的數據類型必須相似，這對于大小和符號都必須匹配的整數類型尤其重要。 即使表存在外鍵約束，MySQL還允許我們刪除表，并且不會產生錯誤(即使這樣做可能會破壞更早創建的外鍵) 刪除外鍵方法： long long ago，人們只能通過刪除表來刪除外鍵。不過現在MySQL(在4.0.13及更高版本中)提供了一種從表中刪除外鍵比較緩和的方法，緩和與否不太清楚，但是至少不再那么無恥。 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) */ 自動鍵更新和刪除： 外鍵可以保證新插入的記錄的完整性。但是，如果在REFERENCES從句中從已命名的表刪除記錄會怎樣？在使用同樣的值作為外鍵的輔助表中會發生什么？ 很明顯，那些記錄也應該被刪除，否則在數據庫中就會有很多無意義的孤立記錄。MySQL可能通過向FOREIGN KEY…REFERENCES修飾符添加一個ON DELETE或ON UPDATE子句簡化任務，它告訴了數據庫在這種情況如何處理孤立任務。 請注意，通過 ON UPDATE　和ON DELETE規則，設置ＭｙＳＱＬ能夠實現自動操作時，如果鍵的關系沒有設置好，可能會導致嚴重的數據破壞。例如，如果一系列的表通過外鍵關系和ON DELETE CASCADE規則連接時，任意一個主表的變化都會導致甚至只和原始刪除有一些將要聯系的記錄在沒有警告的情況下被刪除。所以，我們在操作之前還是要檢查這些規則的，操作之后還要再次檢查。

在MySQL 3.23.44版本后，InnoDB引擎類型的表支持了外鍵約束。 外鍵的使用條件： 1.兩個表必須是InnoDB表，MyISAM表暫時不支持外鍵（據說以后的版本有可能支持，但至少目前不支持）； 2.外鍵列必須建立了索引，MySQL 4.1.2以后的版本在建立外鍵時會自動創建索引，但如果在較早的版本則需要顯示建立； 3.外鍵關系的兩個表的列必須是數據類型相似，也就是可以相互轉換類型的列，比如int和tinyint可以，而int和char則不可以； 外鍵的好處：可以使得兩張表關聯，保證數據的一致性和實現一些級聯操作； 外鍵的定義語法： [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …) REFERENCES tbl_name (index_col_name, …) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] 該語法可以在 CREATE TABLE 和 ALTER TABLE 時使用，如果不指定CONSTRAINT symbol，MYSQL會自動生成一個名字。 ON DELETE、ON UPDATE表示事件觸發限制，可設參數： RESTRICT（限制外表中的外鍵改動） CASCADE（跟隨外鍵改動） SET NULL（設空值） SET DEFAULT（設默認值） NO ACTION（無動作，默認的） 搞個例子，簡單演示一下使用，做dage和xiaodi兩個表，大哥表是主鍵，小弟表是外鍵： 建表： 1CREATE TABLE dage ( 2 id int(11) NOT NULL auto_increment, 3 name varchar(32) default ”, 4 PRIMARY KEY (id) 5) ENGINE=InnoDB DEFAULT CHARSET=latin1； 6 7CREATE TABLE xiaodi ( 8 id int(11) NOT NULL auto_increment, 9 dage_id int(11) default NULL, 10 name varchar(32) default ”, 11 PRIMARY KEY (id), 12 KEY dage_id (dage_id), 13 CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (dage_id) REFERENCES dage (id) 14) ENGINE=InnoDB DEFAULT CHARSET=latin1；

插入個大哥： 1mysql> insert into dage(name) values(‘銅鑼灣’); 2Query OK, 1 row affected (0.01 sec) 3mysql> select * from dage; 4+—-+——–+ 5| id | name | 6+—-+——–+ 7| 1 | 銅鑼灣 | 8+—-+——–+ 91 row in set (0.00 sec)

插入個小弟： 1mysql> insert into xiaodi(dage_id,name) values(1,’銅鑼灣_小弟A’); 2Query OK, 1 row affected (0.02 sec) 3 4mysql> select * from xiaodi; 5+—-+———+————–+ 6| id | dage_id | name | 7+—-+———+————–+ 8| 1 | 1 | 銅鑼灣_小弟A | 9+—-+———+————–+

把大哥刪除： 1mysql> delete from dage where id=1; 2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (bstar/xiaodi, CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (dage_id) REFERENCES dage (id))

提示：不行呀，有約束的，大哥下面還有小弟，可不能扔下我們不管呀！ 插入一個新的小弟： 1mysql> insert into xiaodi(dage_id,name) values(2,’旺角_小弟A’); 2ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bstar/xiaodi, CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (dage_id) REFERENCES dage (id)) 3

提示：小子，想造反呀！你還沒大哥呢！ 把外鍵約束增加事件觸發限制： 1mysql> show create table xiaodi; 2 3 CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (dage_id) REFERENCES dage (id) 4 5mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; 6Query OK, 1 row affected (0.04 sec) 7Records: 1 Duplicates: 0 Warnings: 8mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade; 9Query OK, 1 row affected (0.04 sec) 10Records: 1 Duplicates: 0 Warnings: 0

再次試著把大哥刪了： 1mysql> delete from dage where id=1; 2Query OK, 1 row affected (0.01 sec) 3 4mysql> select * from dage; 5Empty set (0.01 sec) 6 7mysql> select * from xiaodi; 8Empty set (0.00 sec)

得，這回對應的小弟也沒了，沒辦法，誰讓你跟我on delete cascade了呢！ 例子說明的應該蠻清楚了吧，其他功能對應手冊自己實踐吧！:-)