title: Composite primary key or not?
body: <p>Here's what's confusing me. I often have composite primary keys in database tables. The bad side of that approach is that I have pretty extra work when I delete or edit entries. However, I feel that this approach is in the spirit of database design.</p>
  
  <p>On the other side, there are friends of mine, who never use composite keys, but rather introduce another 'id' column in a table, and all other keys are just FKs. They have much less work while coding delete and edit procedures. However, I do not know how they preserve uniqueness of data entries.</p>
  
  <p>For example:<br>
  Way 1</p>
  
  <pre><code>create table ProxUsingDept (
      fkProx int references Prox(ProxID) NOT NULL,    
      fkDept int references Department(DeptID) NOT NULL,    
      Value int,    
      PRIMARY KEY(fkProx,fkDept)
  )
  </code></pre>
  
  <p>Way 2</p>
  
  <pre><code>create table ProxUsingDept (
          ID int NOT NULL IDENTITY PRIMARY KEY
          fkProx int references Prox(ProxID) NOT NULL,    
          fkDept int references Department(DeptID) NOT NULL,    
          Value int
  )
  </code></pre>
  
  <p>Which way is better? What are the bad sides of using the 2nd approach? Any suggestions?</p>