CODEWITHSUNDEEP
mysql
Rami.Q
Rami.Q

Reputation: 2476

why cannot add foreign key in mysql

I have a table named "label" with 2 columns:

_LABEL_ID (varchar)
_LENGTH   (varchar)

This table already has data in it!

And I have several other Tables "a", "b", "c", etc.

these tables have the column _LABEL_ID (varchar). These table are not empty

On delete of any row from tables a, b, c, ...etc., then delete its label from the "label" table. I tried to add foreign key to "label" referenced to table "a" for doing this (just to test it), but getting the same error like in this question:

can't add foreign key in mysql?

In my case I have InnoDB engine for all tables, unique names FK's, same data types.

Now I exported label rows, then emptied the label table, adding foreign key to it referenced to "a" table works just fine. If i import the rows again to the label table, I get the same error which I got when try to add foreign key.

the rows in label table belong to different tables (a, b, c, ..etc). is this the cause of the error?

i do this using MySQL Workbench

EDIT: the real SQL Statement and the Error Message:

ALTER TABLE `cal_view_db`.`dbo_pub_label` 
  ADD CONSTRAINT `fk_dbo_pub_label_treenode`
  FOREIGN KEY (`_LABEL_ID` )
  REFERENCES `cal_view_db`.`dbo_system_treenode` (`_NAME_ID` )
  ON DELETE CASCADE
  ON UPDATE NO ACTION
, ADD INDEX `fk_dbo_pub_label_treenode_idx` (`_LABEL_ID` ASC) ;

Error Message: 

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`cal_view_db`.`#sql-10cc_4a`, CONSTRAINT `fk_dbo_pub_label_treenode` FOREIGN KEY (`_LABEL_ID`) REFERENCES `dbo_system_treenode` (`_NAME_ID`) ON DELETE CASCADE ON UPDATE NO ACTION)
SQL Statement:
ALTER TABLE `cal_view_db`.`dbo_pub_label` 
  ADD CONSTRAINT `fk_dbo_pub_label_treenode`
  FOREIGN KEY (`_LABEL_ID` )
  REFERENCES `cal_view_db`.`dbo_system_treenode` (`_NAME_ID` )
  ON DELETE CASCADE
  ON UPDATE NO ACTION
, ADD INDEX `fk_dbo_pub_label_treenode_idx` (`_LABEL_ID` ASC)

ERROR: Error when running failback script. Details follow.

ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `dbo_pub_label` (
  `_LABEL_ID` varchar(45) NOT NULL,
  `_LENGTH` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`_LABEL_ID`),
  KEY `fk_dbo_pub_label_1_idx` (`_LABEL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

many thanks

p.s: sorry for my bad english!

Upvotes: 3

Views: 1105

Answers (1)

SH-
SH-

Reputation: 1642

Yes, it does seem that your problem is that the data in label belongs to multiple tables.

If I am not mistaken then a FK tells the DB to check in the referenced table for every value in the FK Table.

Here, you have labels from a and b and c and d. When the DB goes to verify the FK it will not find the data in label that corresponds to the data in b,c,d,etc. It will only find the ones that are in a.

This is why your error comes up both when you try and create the table and when you try to reload the data to the table. All records in a FK column must have a match in the referenced table. (Here that is label -> FK -> a)

Upvotes: 1

Related Questions