MySQL: Cannot delete or update a parent row: a foreign key constraint fails

Question

Hi i am trying to make simple database with 2 tables, first for user information, and second for their uploads, because it's project for faculty, i have some assignments... And one is to use foreign key.

DROP TABLE IF EXISTS `korisnici`;

CREATE TABLE `korisnici` (
  `UserID` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(12) NOT NULL,
  `password` VARCHAR(32) NOT NULL,
  `email` VARCHAR(32) NOT NULL,
  `telefon` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`UserID`)
);

DROP TABLE IF EXISTS `slike`;

CREATE TABLE `slike` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(200) NOT NULL,
  `size` INTEGER(11) NOT NULL,
  `type` VARCHAR(200) NULL,
  `file_path` VARCHAR(200) NOT NULL,
  `username` VARCHAR(12) NOT NULL,
  `naslov` VARCHAR(32) NOT NULL,
  `adresa` VARCHAR(80) NOT NULL,
  `opis` VARCHAR(1200) NOT NULL,
  `datum` DATE NOT NULL,
  `UserID` INTEGER(11) NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `slike` ADD FOREIGN KEY (UserID) REFERENCES `korisnici` (`UserID`);


-- ALTER TABLE `korisnici` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `slike` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



-- ---
-- Test Data
-- ---

INSERT INTO `korisnici` (`UserID`,`username`,`password`,`email`,`telefon`) VALUES('1','dd','d','d','d');
INSERT INTO `slike` (`id`,`name`,`size`,`type`,`file_path`,`username`,`naslov`,`adresa`,`opis`,`datum`,`UserID`) VALUES('2','a','122','png','ksks/sss','dsss','aaaa','sss','ssss','2014/09/04','2');

ERROR:

ERROR 1217 (23000) at line 14: Cannot delete or update a parent row: a foreign key constraint fails

Anybody now where is the probel and how could i fix it? It also doesn't work on sqlfiddle when i insert some values for testing. Thanks :)

score 2 · Accepted Answer · answered Sep 06 '14 at 09:19

2

I think the error message is actually misleading. What I see from your code is that it is the insert into SLIKE that fails because UserID=2 does not match the UserID of the previous insert into KORISNICI.

answered Sep 06 '14 at 09:19

Jens Krogsboell

1,093
11
18

score 1 · Answer 2 · edited May 23 '17 at 12:17

You have child record and so since you have put the ON DELETE RESTRICT as well as ON UPDATE RESTRICT constraints(I mean as they are default) whatever changes you make on the parent row i.e. a row in korisnici table with child rows in slike table will be restricted by MySQL.

Now for deletion you can do something like this:

Either change the ON DELETE constraint to CASCADE ... OR ...

Use the following query to delete the record:

DELETE FROM `slike` WHERE `UserID`=`<UserId you want to delete>`;
DELETE FROM `korisnici` WHERE `UserID`=`<UserId you want to delete>`;

And for Updation...

Either change the ON UPDATE constraint to CASCADE ... OR ...

Or else you'll have to write extra database end program (like PL-SQL) in which you will have to take the backup of the child record and then update the parent record and then again insert the child record as per the new updation you have done in the parent record.

Anyways the better option always is to mention the appropriate foeign constraints while specifying or establishing the foreign key.

To get bit more info you can refer this link

ALTER TABLE `slike` ADD FOREIGN KEY (`UserID`) REFERENCES `korisnici` (`UserID`) ON DELETE CASCADE ON UPDATE CASCADE; just that? I do that and still doesnt work :( — Aleksa, Sep 06 '14 at 07:14

score 0 · Answer 3 · answered May 09 '17 at 10:57

There's really no point to choose ON DELETE CASCADE. So the alternative would be to allow the foreign key to be NULL and then choose ON DELETE SET NULL.

Personally I would use "ON UPDATE CASCADE" pared with "ON DELETE SET NULL" to avoid unnecessary complications, but on your set up you may want a different approach.

Hope this helps.

MySQL: Cannot delete or update a parent row: a foreign key constraint fails

3 Answers3