title: MySQL CONSTRAINT on two UNIQUE KEYs, that use the same FOREIGN KEY IDs
body: <p>I want to create a MySQL table to hold relationship data between users. The relationship between user A to B and user B to A can be different.</p>
  
  <p><strong>Example:</strong></p>
  
  <ol>
  <li>Relationship of (from) Bob (to) Alice: 0.9 - Bob loves Alice's stuff.  </li>
  <li>Relationship of (from) Alice (to) Bob: 0.5 - Alice finds Bob's stuff mediocre.</li>
  </ol>
  
  <p><strong>My question:</strong></p>
  
  <p>I have implemented two CONSTRAINTS as UNIQUE KEYs on two FOREIGN KEYS that reference user_ids in a users table. Can I do this? Are they treated as two separate UNIQUE KEYs?</p>
  
  <p>How can I implement a CONSTRAINT that will only allow one occurrence of each (from) UserA (to) UserB relationship and (from) UserB (to) UserA relationship per user_id? Am I going about it the right way?</p>
  
  <p><strong>The SQL:</strong></p>
  
  <pre><code>CREATE TABLE relationships (
    relationship_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_user_id MEDIUMINT UNSIGNED NOT NULL,
    to_user_id MEDIUMINT UNSIGNED NOT NULL,
    relationship_level DECIMAL(1,1) NOT NULL,
    PRIMARY KEY (relationship_id),
  
    FOREIGN KEY (from_user_id) REFERENCES users (user_id)
      ON DELETE CASCADE ON UPDATE NO ACTION,
  
    FOREIGN KEY (to_user_id) REFERENCES users (user_id)
      ON DELETE CASCADE ON UPDATE NO ACTION,
  
    CONSTRAINT from_to_relationship UNIQUE KEY (from_user_id, to_user_id),
    CONSTRAINT to_from_relationship UNIQUE KEY (to_user_id, from_user_id),
  
    INDEX relationship_from_to (relationship_id, from_user_id, to_user_id, relationship_level),
    INDEX relationship_to_from (relationship_id, to_user_id, from_user_id, relationship_level)
  
  ) ENGINE=INNODB;
  </code></pre>
  
  <p>I hope someone can assist. </p>