mysql 外鍵約束

標籤：update   資料庫   mysql   檢測   開發   

(1) 外鍵的使用:

      外鍵的作用,主要有兩個: 
      一個是讓資料庫自己通過外鍵來保證資料的完整性和一致性
      一個就是能夠增加ER圖的可讀性 
      有些人認為外鍵的建立會給開發時操作資料庫帶來很大的麻煩.因為資料庫有時候會由於沒有通過外鍵的檢測而使得開發人員刪除，插入操作失敗.其實這正式外鍵在強制保證資料的完整性和一致性.

(2) 添加外鍵的格式: 
    ALTER TABLE yourtablename 
    ADD [CONSTRAINT 外鍵名] FOREIGN KEY [id] (index_col_name, ...) 
    REFERENCES tbl_name (index_col_name, ...) 
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 
說明: 
on delete/on update,用於定義delete,update操作.以下是update,delete操作的各種約束類型: 
CASCADE: 
       外鍵表中外鍵欄位值會被更新,或所在的列會被刪除. 
RESTRICT: 
      RESTRICT也相當於no action,即不進行任何操作.即,拒絕父表update外部索引鍵關聯列,delete記錄.
set null: 
      被父表的外部索引鍵關聯欄位被update ,delete時,子表的外鍵列被設定為null. 
而對於insert,子表的外鍵列輸入的值,只能是父表外部索引鍵關聯列已有的值.否則出錯.

外鍵定義服從下列情況：(前提條件) 
1) 所有tables必須是InnoDB型，它們不能是暫存資料表.因為在MySQL中只有InnoDB類型的表才支援外鍵.
2) 所有要建立外鍵的欄位必須建立索引. 
3) 對於非InnoDB表，FOREIGN KEY子句會被忽略掉。 
注意: 
建立外鍵時,定義外鍵名時,不能加引號. 
如: constraint ‘fk_1‘ 或 constraint "fk_1"是錯誤的 

(3) 查看外鍵: 
SHOW CREATE TABLE ***;可以查看到建立的表的代碼以及其儲存引擎.也就可以看到外鍵的設定. 
刪除外鍵: 
alter table drop foreign key ‘外鍵名‘. 
注意: 只有在定義外鍵時,用constraint 外鍵名 foreign key .... 方便進行外鍵的刪除. 若不定義,則可以: 
先輸入:alter table drop foreign key -->會提示出錯.此時出錯資訊中,會顯示foreign key的系統預設外鍵名.---> 
用它去刪除外鍵.

(4) 舉例
執行個體一: 
4.1 
CREATE TABLE parent( id INT NOT NULL,  PRIMARY KEY (id) ) TYPE=INNODB;   -- type=innodb 相當於 engine=innodb 
CREATE TABLE child( id INT,  parent_id INT, 
                              INDEX par_ind (parent_id), 
                              FOREIGN KEY (parent_id) REFERENCES parent(id) 
                              ON DELETE CASCADE 
) TYPE=INNODB; 
向parent插入資料後,向child插入資料,插入時,child中的parent_id的值只能是parent中有的資料,否則插入不成功; 
刪除parent記錄時,child中的相應記錄也會被刪除;-->因為: on delete cascade 
更新parent記錄時,不給更新;-->因為沒定義,預設採用restrict. 
4.2 
若child如下: 
mysql> create table child(id int not null primary key auto_increment,parent_id int, index par_ind (parent_id), 
       constraint fk_1 foreign key (parent_id) references 
       parent(id) on update cascade on delete restrict ) type=innodb; 
用上面的: 
1). 則可以更新parent記錄時,child中的相應記錄也會被更新;-->因為: on update cascade 
2). 不能是子表操作,影響父表.只能是父表影響子表. 
3). 刪除外鍵: alter table child drop foreign key fk_1; 
添加外鍵: 
alter table child add constraint fk_1 foreign key (parent_id) references 
parent(id) on update restrict on delete set null; 
4.3

CREATE TABLE `child` (

  `id` int(11) DEFAULT NULL,

  `parent_id` int(11) DEFAULT NULL,

  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.向parent插入資料後,向child插入資料,插入時,child中的parent_id的值只能是parent中有的資料,否則插入不成功; 
2.刪除parent記錄時，刪除無法成功。

3.刪除child中的記錄時。parent的記錄不會被刪除。

4.修改也不能成功

(5) 多個外鍵存在:

product_order表對其它兩個表有外鍵。 
一個外鍵引用一個product表中的雙列索引。另一個引用在customer表中的單行索引： 
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, 
                      price DECIMAL, 
                      PRIMARY KEY(category, id)) TYPE=INNODB; 
CREATE TABLE customer (id INT NOT NULL, 
                      PRIMARY KEY (id)) TYPE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, 
                      product_category INT NOT NULL, 
                      product_id INT NOT NULL, 
                      customer_id INT NOT NULL, 
                      PRIMARY KEY(no), 
                      -- 雙外鍵 
                      INDEX (product_category, product_id), 
                      FOREIGN KEY (product_category, product_id) 
                        REFERENCES product(category, id) 
                        ON UPDATE CASCADE ON DELETE RESTRICT, 
                      -- 單外鍵 
                      INDEX (customer_id), 
                      FOREIGN KEY (customer_id)

                       REFERENCES customer(id)) TYPE=INNODB;

(6) 說明:

1.若不聲明on update/delete,則預設是採用restrict方式. 
2.對於外鍵約束,最好是採用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.

3.查看一個表結構語句show CREATE TABLE child  

mysql   外鍵約束