效果

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,

? `name` varchar(255) NOT NULL,

? `shortdesc` varchar(255) NOT NULL,

? `longdesc` text NOT NULL,

? `address` varchar(255) 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`)

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

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必須一致

比如

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

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

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

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

2.第二種情況

如果兩個表都設置了外鍵

ALTER TABLE `jobs`

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

advertiser

advertisers_ibfk_1advertiser_iddb03jobsadvertiser_idCASCADERESTRICT

jobs

jobs_ibfk_1advertiser_iddb03advertisersadvertiser_idRESTRICTRESTRICT

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

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

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

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

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

執行兩個insert全部報錯

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

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

[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 父表，還是會報錯的，只能先禁用外鍵