title: Identifying Vs Non-Identifying relationships in SQL
body: <p>I've recently started using MySQL Workbench to manage EER diagrams. Up until now I have been using phpmyadmin and I never came across the terms Identifying &amp; Non-Identifying relationships.</p>
  
  <p>I have looked up the difference online and read a few Stack Overflow answers on the topic, but I'm still pretty much clueless.</p>
  
  <p>From what I understand, I am going to give an example of a scenario in my database and I will propose the right solution.</p>
  
  <p>So in my database I have a <code>users</code> table and a <code>contact</code> table containing various methods to contact the user (such as email &amp; phone number). Since a contact record cannot exist without relating to a user,  this should be an <strong>identifying</strong> relationship.</p>
  
  <p>Here is what my tables look like:</p>
  
  <pre><code>+-------+    +-------+
  | users |    |contact|
  +-------+    +-------+
  | id    |    |id     |
  +-------+    |userid |
               |contact| // contains email or phone
               |type   | // specifies if email or phone
               +-------+
  </code></pre>
  
  <p>However, when I create an <strong>identifying</strong> relationship between the two tables, it makes the <code>userId</code> part of a composite key. I understand that the table can have a composite primary key made up of <code>userid</code> and <code>contact</code>, however want to keep a consistent structure throughout the database where every table has its own surrogate key. (I have seen many times that it's better practice to use surrogate keys over composite keys.)</p>
  
  <p>So in this case, what's the actual correct way to do this? Should I use a composite key and scrap the surrogate key (which I really would prefer not to do)? Or should I use a <strong>non-identifying</strong> relationship in this case?</p>
  
  <p>Please explain exactly what the difference between the two relationships is and why <strong>identifying</strong> relationships <em>need</em> to point to a field which is part of a composite key.</p>