mysql外鍵

效果

a,b,c  

如果c設定到a的外鍵，那麼隻能在删除c的記錄後，才能删除a的記錄。

https://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails

CREATE TABLE IF NOT EXISTS `advertisers` (

  `advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `password` char(32) NOT NULL,

  `email` varchar(128) NOT NULL,

  `address` varchar(255) NOT NULL,

  `phone` varchar(255) NOT NULL,

  `fax` varchar(255) NOT NULL,

  `session_token` char(30) NOT NULL,

  PRIMARY KEY (`advertiser_id`),

  UNIQUE KEY `email` (`email`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES

(1, 'TEST COMPANY', '', '', '', '', '', '');

CREATE TABLE IF NOT EXISTS `jobs` (

  `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `advertiser_id` int(11) unsigned NOT NULL,

  `shortdesc` varchar(255) NOT NULL,

  `longdesc` text NOT NULL,

  `time_added` int(11) NOT NULL,

  `active` tinyint(1) NOT NULL,

  `moderated` tinyint(1) NOT NULL,

  PRIMARY KEY (`job_id`),

  KEY `advertiser_id` (`advertiser_id`,`active`,`moderated`)

INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES

(1, 1, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);

ALTER TABLE `advertisers`

  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);

1.第一種情況

advertisers 設定外鍵

advertisers_ibfk_1advertiser_iddb03jobsadvertiser_idCASCADERESTRICT

那麼

删除的話，父和字表都可以删除，如果RESTRICT，父表不能删除

更新和插入的話，必須先插入字表，然後在插入父表，而且advertiser_id必須一緻

比如

(1, 2, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);

(2, 'TEST COMPANY', '', '', '', '', '', '');

2.第二種情況

如果兩個表都設定了外鍵

ALTER TABLE `jobs`

ADD FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`);

advertiser

jobs

jobs_ibfk_1advertiser_iddb03advertisersadvertiser_idRESTRICTRESTRICT

delete from advertisers where advertiser_id='2'; 無法删除

(3, 'TEST COMPANY', '', '', '', '', '', '');

(2, 3, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);

執行兩個insert全部報錯

[SQL]INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`db03`.`jobs`, CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`))

外鍵總結

1.CASCADERESTRICT的差別

2.如果是RESTRICT，insert必須先插入字表，就是沒有外鍵的表

3.如果是RESTRICT，delete必須先删父表，然後再删除子表

4.如果是整個drop 父表，還是會報錯的，隻能先禁用外鍵

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1971646，如需轉載請自行聯系原作者