SQL Primary Key - is it necessary?
I have a list of items. Most of these items will not be in stock. The item table has id, name, description. The quantities of items are stored in another table named inventory. The inventory table has item_id and quantity of items that are in stock.
Do I need a primary key for the inventory table? If so, should I use a serial or composite key? When is it ok for a table to not have a primary key?
Edit: Thank you all for being very informative. I will now always have primary keys except in very rare exceptions. I also learned a bit more about serial vs composite keys.
Answer
Always aim to have a primary key.
If you are unsure, have a primary key.
Even if you are 99.99% sure you will not need it, have one. Requirements change as I have learned through experience over many years.
The only examples I can really think of are many-to-many tables with just two foreign_keys and mega-huge (hundreds of millions of rows) tables where every byte counts. But even then a separate, unique, no-business value id key is still strongly recommended.
There's some more great info on this here:
http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
and here:
http://www.techrepublic.com/article/the-great-primary-key-debate/1045050
here:
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
and here:
Should I use composite primary keys or not?
In your example, I would definitely have one.
The decision to 'not' have one should be based on a very clear need and understanding and actual or predicted (e.g. volume) issues with having one.
One great example of this need comes up when debugging and troubleshooting. Just like having create and update columns in each table (another favorite of mine), this info may not initially be used by/for the front end but boy can it be helpful in tracing and resolving issues. (btw update stamps are often now standard in frameworks like Ruby On Rails which also works well with the convention of every table having an id field!)