Databases
StackOverflow2013
Postsdbo
POShould I use composite primary keys or not?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POShould I use composite primary keys or not?
    text
    copied!<p>There seems to only be 2nd class support for composite database keys in Java's JPA (via EmbeddedId or IdClass annotations). And when I read up on composite keys, regardless of language, people keep coming across as them being a bad thing. But I cannot understand why. Are composite keys still acceptable to use these days? If not, why not?</p> <p>I've found one person who agrees with me: <a href="http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx" rel="nofollow noreferrer">http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx</a></p> <p>But another who doesn't: <a href="http://weblogs.java.net/blog/bleonard/archive/2006/11/using_composite.html" rel="nofollow noreferrer">http://weblogs.java.net/blog/bleonard/archive/2006/11/using_composite.html</a></p> <p>Is it just me, or are people not able to make the distinction of where a composite key is appropriate or not? I see composite primary keys useful when the table doesn't represent an entity - i.e. when it represents a join table.</p> <p>A simple example:</p> <p><code> Actor { Id, Name, Email } Movie { Id, Name, Year } Character { Id, Name } Role { Actor, Movie, Character } </code></p> <p>Here Actor, Movie and Character obviously benefit from having an Id column as the primary key.</p> <p>But Role is a Many-To-Many join table. I see no point in creating an id just to identify a row in the database. To me it seems obvious that the primary key is <code>{ Actor, Movie, Character }</code>. It also seems like a rather limiting feature, especially if the data in the join table changes all the time, you could find yourself with primary key collisions once the primary key sequence wraps around to 0.</p> <p>So, back to the original question, is it still acceptable practice to use composite primary keys? If not, why not?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload