How can I find the invalid keys in a MySQL table?

Question

I have a MySQL table and I want to add a foreign key constraint.

In this case it's a self-referencing key...

CREATE TABLE `contracts` (
  `id` int(11) NOT NULL auto_increment,
  `contract_id_amend_source` int(11) default NULL,

  # ...

  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14834 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

(I'm working with a legacy DB here -- I didn't come up with the column names.)

I try to apply the key like this...

ALTER TABLE contracts
ADD CONSTRAINT fk_parent_id
FOREIGN KEY ( contract_id_amend_source )
REFERENCES contracts( id )
ON DELETE CASCADE;

But I get an error...

Cannot add or update a child row: a foreign key constraint fails (contract_tracker/#sql-e18_e9d0a, CONSTRAINT fk_parent_id FOREIGN KEY (contract_id_amend_source) REFERENCES contracts (id) ON DELETE CASCADE)

Ok, there are some invalid parent_ids.

I try to find them like this...

SELECT id, contract_id_amend_source
FROM contracts
WHERE contract_id_amend_source
NOT IN (
  SELECT id
  FROM contracts
);

That returns about 20 records. I fix the keys manually. Then run the above query again. Now it returns no records.

I try to ADD the FK again, but I still get the "Cannot add or update..." error.

I guess my query to find the bad keys must be wrong? How can I fix it?

Please include the exact foreign key error, as well as the full column definitions for contracts.id and contracts.parent_id. — Michael - sqlbot, Nov 06 '13 at 01:11

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

2

Have a look at truncate foreign key constrained table.

Specifically, if you are only just changing the table's data, you can use:

SET FOREIGN_KEY_CHECKS=0;
... do whatever you need here that is giving you problems...
SET FOREIGN_KEY_CHECKS=1;

I have this one bookmarked, it is very useful.

You may also try to find the additional hidden records by:

SELECT id 
FROM contracts 
LEFT JOIN contracts  
ON contracts.id=contracts.parent_id
WHERE contracts.id IS NOT NULL 
AND
contracts.parent_id IS NULL;

Just a quick not about @Michael - sqlbot's comment... he is absolutely correct. This is normally not an issue when truncating (you don't care about the data integrity), but in this case you do care, so the first part of this answer is a half solution at best.

edited May 23 '17 at 12:13

Community

1
1

answered Nov 06 '13 at 00:59

Sablefoste

4,032
3
37
58

If he truncates the table he'll lose all his data. – Barmar Nov 06 '13 at 01:00
True, I meant that he should perform whatever he is doing in between the foreign_key_checks. I updated the answer. – Sablefoste Nov 06 '13 at 01:10
You don't want to do this, even if it "works" because it will allow invalid data to persist in the table, which will come back to bite, later. – Michael - sqlbot Nov 06 '13 at 01:13

How can I find the invalid keys in a MySQL table?

1 Answers1