SQL Primary Key - is it necessary?

Question

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.

Professoriate

ANY real data table needs a primary key - that's the way to uniquely identify each row in your table. Why would you ever not want to have a PK is beyond me..... the only case I don't have a primary key on a table might be a temporary table to bulk load data or something like that... – Spacetime

@mark_s No any connection to the question, but I have the private opinion, that in very PARTICULAR cases I can have and utilize heaps better, than PK-based tables 8-) Lets go to chat? – Inapprehensive

@mark_s Yep, exactly, only bulk loaded heaps rules 8-) – Inapprehensive

@OlegDok: staging tables is the only realistic case for heaps/tables without clustered PKs – Gormley

@Gormley Thats exactly what I meant, there will be a question in a short time @ dba.se about the PK – Inapprehensive

@Oleg, It ought to go without saying but clustered indexes have nothing to do with PKs. You can (and should) have a key whether or not your table is clustered. – Contain

Answer 1

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!)

Appease

The only examples I can really think of are many-to-many tables with just two foreign_keys - why not make the two foreign keys into composite primary keys as well? – Whoopee

Answer 2

Do I need a primary key for the inventory table?

Can we assume the data is relational? A relation has no duplicate tuples by definition. SQL allows duplicate rows in a table. Therefore, to ensure no duplicate rows in practice, every table should have at least one unique constraint. To cut a long story short, you better have a good reason for not placing a unique constraint on every candidate key in the table. By definition, zero or one candidate key may be designated 'primary' and which one (if any) should receive this designation is arbitrary.

should I use a serial or composite key?

I think this is a typo. A single-column key is known as a "simple key" and not "serial key". From your description, your Inventory table has a sole candidate candidate key on item_ID which is a simple key. The only possible composite key is a superkey and, unless it is to be referenced by a foreign key, should not be constrained using a unique constraint.

When is it ok for a table to not have a primary key?

When all candidate keys have been constrained using UNIQUE constraints or when the table is not intended to hold relational data.

Wendish

Answer 3

In general: Every table should have a PK. At least every table should have some CLUSTER index. The PK must not be one special column, but having rows without unique identification in system (RDBMS) is not good practise.

There can be several cases where PK is not required, but that are exceptions in rule.

Carmelinacarmelita

A clustered index is a physical implementation detail of an index that is completely irrelevant for the question whether a PK is needed or not. And besides: not every DBMS has clustered indexes – Halloran

a_horse_with_no_name: If the DBMS has no user-defined cluster indexes, then the DBMS has some own internal way, how to cluter the data. Of course - if your DBMS is that case, ignore my note about CLUSTER for that DBMS :). – Carmelinacarmelita

Answer 4

If item_id is unique in the inventory table, I'd say you're fine with using that as an identifier. A primary key is usually used to uniquely identify a line, but there is no use for an inventory line identity in your case that I can see.

EDIT: As others have noted, generally if you don't have a good reason for a primary key, you'll be well off looking at your table structure to see if you can merge it with another table, in this case probably the items table. I can see cases where that's not an option (for example that you can't change the schema, just add new tables) but it's worth a look.

Rickyrico

Answer 5

If you have only one inventory row per item - then it will be much cheaper (mean - CPU and IO) that it will be in the same Item table,

if not - it depends. And it will not be a normalized data at all

But as far as I understand the question and if you insist on two tables - yes, its better to have an index on item_id field

Inapprehensive

I'm still mulling over whether I should merge the two tables together, something about that feels wrong. It feels like the entities being described in each table are similar but not quite the same. – Professoriate

Two tables have reason only if you have something like delivery of each item with its separate price, else you can easily merge them together – Inapprehensive

Hmm alright your points are taken. I will merge them together. I've concluded that quantity falls within the item entity. My reasoning is that the item table describes an item in each row and that quantity is still describing the item. Perhaps I'll rename the table to Items as each row describes items more than a single item... or not since 1 items is pretty stupid. – Professoriate

Although you made interesting points, Michael answered all three questions. – Professoriate

I'm guessing English is not your first language but I suspect there are deeper problems here. Normalising to the highest possible normal form, being 6NF, seems to require at least three tables, probably more. I don't see how using two tables would violate 1NF at all. – Wendish