title: How can I find the invalid keys in a MySQL table?
body: <p>I have a MySQL table and I want to add a foreign key constraint.</p>
  
  <p>In this case it's a self-referencing key...</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>(I'm working with a legacy DB here -- I didn't come up with the column names.)</p>
  
  <p>I try to apply the key like this...</p>
  
  <pre><code>ALTER TABLE contracts
  ADD CONSTRAINT fk_parent_id
  FOREIGN KEY ( contract_id_amend_source )
  REFERENCES contracts( id )
  ON DELETE CASCADE;
  </code></pre>
  
  <p>But I get an error...</p>
  
  <blockquote>
    <p>Cannot add or update a child row: a foreign key constraint fails (<code>contract_tracker/#sql-e18_e9d0a</code>, CONSTRAINT <code>fk_parent_id</code> FOREIGN KEY (<code>contract_id_amend_source</code>) REFERENCES <code>contracts</code> (<code>id</code>) ON DELETE CASCADE)</p>
  </blockquote>
  
  <p>Ok, there are some invalid <code>parent_id</code>s.</p>
  
  <p>I try to find them like this...</p>
  
  <pre><code>SELECT id, contract_id_amend_source
  FROM contracts
  WHERE contract_id_amend_source
  NOT IN (
    SELECT id
    FROM contracts
  );
  </code></pre>
  
  <p>That returns about 20 records. I fix the keys manually. Then run the above query again. Now it returns no records.</p>
  
  <p>I try to <code>ADD</code> the FK again, but I still get the "Cannot add or update..." error.</p>
  
  <p>I guess my query to find the bad keys must be wrong? How can I fix it?</p>