MySQL Forums
Forum List  »  InnoDB

Strange Foreign key error
Posted by: Christophe Derenne
Date: April 12, 2016 03:23PM

Hi
I'm using foreign key since 15 years ago with other SGDB
But i'm a little new with mysql

So there is a trouble that does not happen on other SGBD
Got 2 tables 1 parent and 1 child

CREATE TABLE TBPARENT (
`COLP1` VARCHAR(10) NOT NULL,
`COLP2` INT NOT NULL,
PRIMARY KEY (`COLP1`, `COLP2`));

With 2 rows:
INSERT INTO `TBPARENT` (`COLP1`, `COLP2`) VALUES ('A', '0');
INSERT INTO `TBPARENT` (`COLP1`, `COLP2`) VALUES ('A', '1');

CREATE TABLE `TBCHILD` (
`COLCH1` INT NOT NULL,
`COLCH2` VARCHAR(10) NOT NULL,
`COLCH3` VARCHAR(45) NULL,
PRIMARY KEY (`COLCH1`));

With 1 row :
INSERT INTO `TBCHILD` (`COLCH1`, `COLCH2`, `COLCH3`)
VALUES ('100', 'A', 'ABC');

Now let's create a FK on COLCH2 references by TBPARENT(COLP1) like this :

ALTER TABLE `TBCHILD`
ADD INDEX `FK_CHILD_PARENT_idx` (`COLCH2` ASC);
ALTER TABLE `TBCHILD`
ADD CONSTRAINT `FK_CHILD_PARENT`
FOREIGN KEY (`COLCH2`)
REFERENCES `TBPARENT` (`COLP1`)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Now try this :
DELETE FROM TBPARENT WHERE COLP2=0

It must works because after that, a row of TBPARENT containing 'A' in COLP1 will still exist.

But i've got :
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`TBCHILD`, CONSTRAINT `FK_CHILD_PARENT` FOREIGN KEY (`COLCH2`) REFERENCES `TBPARENT` (`COLP1`) ON DELETE NO ACTION ON UPDATE CASCADE) 0.062 sec

Why ??

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange Foreign key error
1857
April 12, 2016 03:23PM
716
April 12, 2016 07:02PM
698
April 13, 2016 08:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.