mysql中外來鍵約束級聯更新與刪除

jasnet_u發表於2016-09-10
MySql


預設情況下，mysql的外來鍵約束型別是restrict (no action)型別 ，表示一般的外來鍵約束。


ON DELETE CASCADE ON UPDATE CASCADE   可以設定外來鍵為級聯更新、級聯刪除。

當主表的關聯欄位發生變更時，從表自動做相應的改變。

級聯刪除：當主表的關聯記錄被刪除時，從表會自動刪除與主表刪除記錄相關聯的記錄。

級聯更新:   當主表的記錄(一般是主鍵值)發生更新時，從表自動更新自己的外來鍵值。

示例：

建立t_user表


CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

建立  t_address

一個使用者能有多個收貨地址,所以使用者到地址是一對多的關係，並設定級聯更新與級聯刪除。


CREATE TABLE `t_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `province` varchar(32) DEFAULT NULL,
  `city` varchar(32) DEFAULT NULL,
  `detialAddress` varchar(200) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `t_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) 

  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

插入一些測試資料



 INSERT INTO t_user (NAME) VALUES 
 ('張三'),('李四'),('王五'); 
 
 INSERT INTO t_address(province,city,detialAddress,user_id) VALUES
 ('湖北','武漢','雄楚大道101號',1),
 ('湖北','武漢','關山大道110號',1),
 ('湖北','武漢','珞喻路92號',1),
 ('湖南','長沙','aaa路aaa號',2),
 ('湖南','長沙','aaa路bbb號',2),
 ('湖南','長沙','aaa路ccc號',2)   ;


這時發現使用者表中3條記錄，地址表中6表條記錄。張三有3個收貨地址，李四也是，王五沒有收貨地址。

這時，更新使用者表中id為1的記錄，更新id值為10 ，這時發現地址表中前3條記錄的外來鍵(user_id)值都從1更新為了10

 UPDATE  t_user a SET a.`id` =10  WHERE a.`id`=1;  #執行這個sql語句時不會報外來鍵約束的錯，因為從表外來鍵列上設定了級聯更新。


如果主表執行刪除   ，則會自動刪除從表中的相應記錄。

 DELETE FROM t_user WHERE t_user.`id`=1 ; #執行時不會報錯，因為從表外來鍵上設定了級聯刪除。


這時從t_address表中的，前3條記錄就被級聯刪除了。 


注意：

如果主從表只是普通的外來鍵約束，則在更新/刪除主表記錄時，需要先更新/刪除從表記錄，否則將會報外來鍵約束異常，導致操作失敗。

。。。。。。


相關文章