Mysql foreignkey 相關

標籤：名稱   AC   for   記錄   statement   splay   use   指定   ide   

foreignkey  是指一對多關聯性時建立的關係：

 外鍵是表中與另一個表的另一個欄位匹配的欄位。外鍵對相關表中的資料施加限制，這使MySQL能夠保持參照完整性。　　

操作：

　　

能看懂圖嗎？

 

下面直接看代碼：

　　

我們有兩個表：customers和orders. 每個客戶都有零個或多個訂單，每個訂單只能屬於一個客戶。customers表和orders表之間的關係是一對多關聯性，並且orders由該customerNumber欄位指定的表中的外鍵建立。表中的customerNumber欄位與orders表中的customerNumber主鍵欄位相關  customers。

該customers 表稱為父表或被參考資料表，該orders表稱為子表或參考資料表。

外鍵可以是一列或一組列。子表中的列通常引用父表中的主鍵列。

一個表可能有多個外鍵，並且子表中的每個外鍵都可能引用不同的父表。

子表中的行必須包含父表中存在的值，例如，表中的每個訂單記錄orders必須具有表customerNumber中存在的值customers。因此，多個訂單可以指同一個客戶，這種關係被稱為一個（客戶）多個（訂單）或一對多。

有時，子表和父表是相同的。外鍵返回到表的主鍵，例如下employees表：

該reportTo列是一個外鍵，它指的employeeNumber是employees表格的主鍵，以反映員工之間的報告結構，即每個員工向另一個員工報告，員工可以有零個或多個直接報告。我們有一個關於自我聯結的特定教程，可以協助您根據這種表格式查詢資料。

該reportTo外鍵也被稱為遞迴或自引用的外鍵。

外鍵實施參照完整性，可協助您自動維護資料的一致性和完整性。例如，您無法為不存在的客戶建立訂單。

另外，您可以為customerNumber外鍵設定串聯刪除操作，這樣當您刪除customers表中的客戶時，與客戶關聯的所有訂單也會被刪除。這為您節省了使用多個DELETE語句 或DELETE JOIN語句的時間和精力。

與刪除相同，還可以定義串聯更新操作，以便customerNumber外鍵執行跨表更新，而不使用多個UPDATE語句或UPDATE JOIN語句。

在MySQL中，InnoDB 儲存引擎支援外鍵，因此您必須建立InnoDB表才能使用外鍵約束。

 

 

 

添加外鍵代碼：

　　

CONSTRAINT constraint_nameFOREIGN KEY foreign_key_name (columns)REFERENCES parent_table(columns)ON DELETE actionON UPDATE action

View Code

• 該CONSTRAINT子句允許您為外鍵約束定義約束名稱。如果你忽略它，MySQL會自動產生一個名字。
• 該FOREIGN KEY子句指定子表中引用父表中主鍵列的列。你可以在FOREIGN KEY子句之後放置一個外鍵名，或者讓它為MySQL建立一個名字。注意MySQL會自動建立一個帶有foreign_key_name名字的索引。
• 該REFERENCES子句指定父表和它的子表中的列引用的列。在規定的子表和父表的列數FOREIGN KEY和REFERENCES必須相同。
• 該ON DELETE子句允許您定義刪除父表中的記錄時，子表中記錄發生的情況。如果您省略了ON DELETE子句並刪除了在子表中具有記錄的父表中的記錄，那麼MySQL將拒絕刪除。此外，MySQL還為您提供了一些操作，以便您可以使用其他選項，例如ON DELETE CASCADE  ，要求MySQL刪除子表中引用父表中的記錄時父表中的記錄被刪除的記錄。如果您不希望刪除子表中的相關記錄，則可以使用該ON DELETE SET NULL操作。MySQL會將子表中的外鍵列值設定為NULL當父表中的記錄被刪除時，使用子表中的外鍵列必須接受NULL值的條件。請注意，如果您使用ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL將拒絕刪除。
• 該ON UPDATE子句允許您指定更新父表中的行時發生的子表中的行。ON UPDATE當父表中的行更新時，可以省略該子句以使MySQL拒絕對子表中的行進行任何更新。該ON UPDATE CASCADE操作允許您執行交叉表更新，當父表ON UPDATE SET NULL中的行更新時，該操作會將子表中的行中的值重設為值NULL。該ON UPDATE NO ACTION或UPDATE RESTRICT行動拒絕任何更新。

 

 

 

MySQL建立表外鍵樣本

下面的樣本建立一個dbdemo資料庫和兩個表：categories和  products.每個類別具有一個或多個產品和每個產品只屬於一個類別。表中的cat_id欄位products被定義為帶有UPDATE ON CASCADE和DELETE ON RESTRICT動作的外鍵。

 

CREATE DATABASE IF NOT EXISTS dbdemo; USE dbdemo; CREATE TABLE categories(   cat_id int not null auto_increment primary key,   cat_name varchar(255) not null,   cat_description text) ENGINE=InnoDB; CREATE TABLE products(   prd_id int not null auto_increment primary key,   prd_name varchar(355) not null,   prd_price decimal,   cat_id int not null,   FOREIGN KEY fk_cat(cat_id)   REFERENCES categories(cat_id)   ON UPDATE CASCADE   ON DELETE RESTRICT)ENGINE=InnoDB;

View Code將外鍵添加到表中MySQL添加外鍵文法

要將外鍵添加到現有表中，可以使用ALTER TABLE語句和上面的外鍵定義文法：

ALTER table_nameADD CONSTRAINT constraint_nameFOREIGN KEY foreign_key_name(columns)REFERENCES parent_table(columns)ON DELETE actionON UPDATE action;

View CodeMySQL添加外鍵樣本

現在，我們添加一個名為的新表，vendors並更改products表以包含供應商ID欄位：

USE dbdemo; CREATE TABLE vendors(    vdr_id int not null auto_increment primary key,    vdr_name varchar(255))ENGINE=InnoDB; ALTER TABLE products ADD COLUMN vdr_id int not null AFTER cat_id;

View Code

要將外鍵添加到products表中，請使用以下語句：

2345ALTER TABLE productsADD FOREIGN KEY fk_vendor(vdr_id)REFERENCES vendors(vdr_id)ON DELETE NO ACTIONON UPDATE CASCADE;

View Code

現在，該products表有兩個外鍵，一個categories表指向表，另一個指向vendors表。

刪除MySQL外鍵

您還可以使用該ALTER TABLE語句刪除外鍵，如下所示：

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

View Code

在上面的聲明中：

• 首先，指定要從中刪除外鍵的表名。
• 其次，您將約束名稱放在該DROP FOREIGN KEY子句之後。

注意，這constraint_name是在建立或向表中添加外鍵時指定的約束的名稱。如果你忽略它，MySQL會為你產生一個約束名稱。

要擷取表的產生約束名稱，請SHOW CREATE TABLE按如下所示使用該語句：

 

SHOW CREATE TABLE table_name;

例如，要查看products表的外鍵，可以使用以下語句：

    SHOWS  CREATE TABLE products;

 

 

 

On Delete和On Update都有Restrict，No Action, Cascade,Set Null屬性。現在分別對他們的屬性含義做個解釋。

• ON DELETE

restrict(約束):當在父表（即外鍵的來源表）中刪除對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除。

no action:意思同restrict.即如果存在從資料，不允許刪除主要資料。

cascade(級聯):當在父表（即外鍵的來源表）中刪除對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則也刪除外鍵在子表（即包含外鍵的表）中的記錄。

set null:當在父表（即外鍵的來源表）中刪除對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則設定子表中該外索引值為null（不過這就要求該外鍵允許取null）

• ON UPDATE

restrict(約束):當在父表（即外鍵的來源表）中更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許更新。

no action:意思同restrict.

cascade(級聯):當在父表（即外鍵的來源表）中更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則也更新外鍵在子表（即包含外鍵的表）中的記錄。

set null:當在父表（即外鍵的來源表）中更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則設定子表中該外索引值為null（不過這就要求該外鍵允許取null）。

註：NO ACTION和RESTRICT的區別：只有在及個別的情況下會導致區別，前者是在其他約束的動作之後執行，後者具有最高的優先權執行。

Mysql foreignkey 相關