title: Foreign Key Referencing Same Table
body: <p>I've searched well and can't find an answer explaining the problem well.  Here is a simplified example of my issue.  Let's say I have a table <code>test</code> with two fields <code>first</code> and <code>second</code>.  I make <code>first</code> the primary key and want <code>second</code> to reference <code>first</code> and cascade on update.  This will allow me to make a list of rows, knowing which row comes after any given row, unless the 'head'.</p>
  
  <p>Creating the table works fine, however when I try to add a row it complains that the foreign key constraint with cascading update failed.  This makes sense, as I left <code>second</code> as null.  Therefore, I thought I could insert two rows and then add the foreign key:</p>
  
  <ol>
  <li><code>first: a     second: b</code></li>
  <li><code>first: b     second: a</code></li>
  </ol>
  
  <p>This was successful.  I then tried updating row 1 to have a <code>first</code> value of <code>c</code>.  However, this didn't work, stating the foreign key failed.  Why?  Shouldn't it just become the following?</p>
  
  <ol>
  <li><code>first: c     second: b</code></li>
  <li><code>first: b     second: c</code></li>
  </ol>
  
  <p>My only hunch is the existence of circular updates with the update to row 1 cascading to row 2, cascading back to row 1, but this shouldn't happen!  There is nothing referencing <code>second</code>, so the update should just cascade to row 2 and stop.  Please help, this is driving me crazy.</p>
  
  <p>Edit: As requested, here's my <code>show create table test</code>.</p>
  
  <pre><code>CREATE TABLE `test` (
   `first` varchar(32) NOT NULL,
   `second` varchar(32) NOT NULL,
  PRIMARY KEY (`first`),
  KEY `fk` (`second`),
  CONSTRAINT `fk` FOREIGN KEY (`second`) REFERENCES `test` (`first`) ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  </code></pre>