Primary Key .. is it really necessary?

Lets say my tables look like this:



..must I have a primary key set for the orders? I can't set either field as a primary key because PKs must be UNIQUE while a customer may place several orders or an item may be purchased by multiple customers.

Why must there be a primary key for every table? (I have created tables without PKs on multiple occasions) It seems like a waste of space to setup an auto-inc id field when I can't see a use for it.

Using a primary key whenever possible is a good idea. It doesn't always have to be an auto_incrementing integer either. The point is to give that one record a unique field to set it apart from the rest. Generally, I'll use a non-integer primary key when I need to make sure that a field containing text can't be duplicated (username, email address, etc). I'll always use an integer primary key when I need to refrence something quickly say ... through a url (category id, product id, order id, etc).

As far as I know, the difference between the a primary key() field and a unique() field is that a primary key automaticly has an index on it.

As kingmike already said, using primary keys wherever possible is a very good idea due to two reason:

1. Indizes

This is database depended, some database automaticly generate an index on every primary key column. This gives your table a huge speed increasement (even on tables with little data), but as our data grows, you can't live w/o indizes.

2. Referential integrity.

A primary key implies a unique column, so you don't have to worry about duplicate entries on it since it's just another constraint.

Also you need primary keys in order to create foreign key constraints (just as kingmike described, but e.g. with MSSQL you don't create other primary key's on your referencing columns but a foreign key)

I really think this is a question of database design, not just PK usage.

At one point there was a serious attempt by relational database creators to enforce uniqueness on every table. In other words, their point was: if you can't think of a reason why every row of a table should be unique, then you haven't thought through your database design properly.

Even though most SQL DB systems don't enforce uniqueness, I make sure that every table I design has a unique key. "Saying something twice doesn't make it any more true." (C.J. Date). Thus having the same identical record more than once doesn't provide any benefit at all, and is a way to invite confusion into your data.

In your case, I think you are missing a table. You should use the Order table only as a marker for each unique order amount and description, while the orders table would also have a foreign key relationship with an order_details table, which has its own independent primary key, as well as a foreign key matching order IDs and a foreign key matching product IDs. This way, the 'orders' table would handle the main invoice total, while the order_details table would show the line-item details.