title: MySQL ERROR 1072: States Key doesn't exist, yet it does exist
body: <p>Apologies if this is a newbie question, but I have been unable to resolve this problem for some time and am hoping someone with a better understand of MySQL can assist me in deciphering the following problem:<br><br>
  I have two tables that I want to maintain so that users can define their preferences, while an admin can maintain their access rights to those preferences. And I want to make sure that if an admin removes certain rights, that the preferences table gets updated:<br><br>
  The two tables are:</p>
  
  <pre><code>main_access
  +--------------+---------+------+-----+---------+-------+
  | Field        | Type    | Null | Key | Default | Extra |
  +--------------+---------+------+-----+---------+-------+
  | nav_location | int(11) | NO   | PRI | NULL    |       |
  | user_id      | int(11) | NO   | PRI | NULL    |       |
  +--------------+---------+------+-----+---------+-------+
  </code></pre>
  
  <p>and</p>
  
  <pre><code>main_prefs
  +--------------+---------+------+-----+---------+-------+
  | Field        | Type    | Null | Key | Default | Extra |
  +--------------+---------+------+-----+---------+-------+
  | nav_location | int(11) | NO   | PRI | NULL    |       |
  | user_id      | int(11) | NO   | PRI | NULL    |       |
  +--------------+---------+------+-----+---------+-------+
  </code></pre>
  
  <p>They both have unique indexes, as I run this:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS main_access (
      nav_location    integer not null,
      user_id         integer not null,
      unique index uk_access (nav_location, user_id)
  );
  
  CREATE TABLE IF NOT EXISTS main_pref (
      nav_location    integer not null,
      user_id         integer not null,
      unique index uk_pref (nav_location, user_id)
  );
  </code></pre>
  
  <p>So, uk_access, and uk_pref with the above tables are being created. Yet, when I run:</p>
  
  <pre><code>ALTER TABLE main_pref 
  ADD CONSTRAINT FK_access1 
  FOREIGN KEY(uk_pref) REFERENCES main_access(uk_access) 
  ON UPDATE CASCADE 
  ON DELETE CASCADE;
  </code></pre>
  
  <p>I get the "ERROR 1072 (42000): Key column 'uk_pref' doesn't exist in table" error. Which, to me, makes zero sense to get. I know the key exists, as I also ran the following after creating the tables:</p>
  
  <pre><code>ALTER TABLE main_pref ADD UNIQUE INDEX uk_pref (nav_location, user_id);
  </code></pre>
  
  <p>and got a duplicate key warning. I did the same thing with the other table.<br>
  I also tried making the unique index name the same for both tables. That is, I made them both <code>uk_pref</code>, to no avail. Same error.<br>
  I feel like this is a really simple problem that I'm missing, but just haven't been able to see it. I am hoping another set of eyes will point out the mistake I'm making. Any help is much appreciated!<br><br>
  I looked for similar problems, but could not find any on here that addressed the same issue.</p>
  
  <p>EDIT: AJ pointed this out, and I forgot to mention that I tried it:<br>
  I also (first) tried bypassing setting unique keys and did the following:</p>
  
  <pre><code>ALTER TABLE main_pref 
  ADD CONSTRAINT FK_access1 
  FOREIGN KEY(user_id, nav_location) REFERENCES main_access(user_id, nav_location) 
  ON UPDATE CASCADE 
  ON DELETE CASCADE;
  </code></pre>
  
  <p>And MySQL just came back a syntax error.</p>