MySQL problem to add FOREIGN KEY?

Question

I've got 2 innodb tables, here it is with SHOW CREATE TABLE query:

| top_menu | CREATE TABLE `top_menu` (
  `t_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `menu_photo` char(128) NOT NULL,
  `title` char(64) NOT NULL,
  `atdc_id` int(10) unsigned NOT NULL,
  `menu_order` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

| attendance | CREATE TABLE `attendance` (
  `atdc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(128) NOT NULL,
  `content` text,
  `price` double NOT NULL,
  `sale_percent` tinyint(3) unsigned NOT NULL,
  `atdc_order` int(10) unsigned NOT NULL,
  `s_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`atdc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The result of doing, normal adding FOREIGN KEY is an error. Query: ALTER TABLE top_menu ADD FOREIGN KEY (atdc_id) REFERENCES attendance(atdc_id); Error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (database., CONSTRAINT #sql-a36a_5c109d2_ibfk_1 FOREIGN KEY (atdc_id) REFERENCES attendance (atdc_id))

What should I do with this? It always worked for me well.

Alex Smirnoff · Accepted Answer · 2011-07-17 17:08:10Z

2

It seems that some top_menu rows have the atdc_id which does not exist in attendance table.

answered Jul 17, 2011 at 17:08

Alex Smirnoff

4864 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Smirnoff Over a year ago

I am talking about this, for example: top_menu has rows with atdc_id: 1,2,3,4; but attendance table has rows with atdc_id: 1,2 and 3, that's all (there is no atdc_id=4), so you can not create foreign key.

regilero · Accepted Answer · 2011-07-17 17:26:01Z

0

in complement to @Alexey Smirnoff answer: When are you adding the foreign key? If it is from a restore script ensure you have the attendance table content loaded before you perform this query. Or delay foreign key checks to the end of the process.

If you want to find which rows are giving you this problem run this query:

Select a.* 
from top_menu a
left join attendance b
on a.atdc_id = b.atdc_id
where b.atdc_id IS NULL;

answered Jul 17, 2011 at 17:26

regilero

30.7k6 gold badges65 silver badges101 bronze badges

Collectives™ on Stack Overflow

MySQL problem to add FOREIGN KEY?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related