title: Composite primary key comprising two foreign keys referencing same table: SQL Server vs. MySQL
body: <p>I've read over a number of posts regarding DB table design for a common one-to-many / users-to-friends scenario.  One post included the following:</p>
  
  <blockquote>
    <p>USERS</p>
  
  <pre><code>* user_id (primary key)
  * username
  </code></pre>
    
    <p>FRIENDS</p>
  
  <pre><code>* user_id (primary key, foreign key to USERS(user_id))
  * friend_id (primary key, foreign key to USERS(user_id))
  </code></pre>
    
    <p><strong>> This will stop duplicates (IE: 1, 2)
    from happening, but won't stop
    reversals because (2, 1) is valid.
    You'd need a trigger to enforce that
    there's only one instance of the
    relationship...</strong></p>
  </blockquote>
  
  <p>The bold portion motivated me to post my question: is there a difference between how SQL Server and MySQL handle these types of composite keys?  Do both require this trigger that the poster mentions, in order to ensure uniqueness?  </p>
  
  <p>I ask, because up until this point I've been using a similar table structure in SQL Server, without any such triggers.  Have I just luckily not run into this data duplication snake that's lurking in the grass?</p>