Composite Primary key verses one column based primary key

Question

Dear All,

I have gone through link:

http://stackoverflow.com/questions/1264248/database-design-composite-key-vs-one-column-primary-key

Mine question:

For a table, which can never be referenced as Foreign Key to any other table, what are +ve / -ve aspect in term of insertion/updation of having single column based primary key verses multi-column/composite column primary key?

Thanks

Answer 1

Yes, a multi-column primary key is still a bad choice, if:

• your primary key is also the clustering key
• your table also contains other non-clustered indices

Because:

• the value(s) from the clustering key (e.g. the single INT column, or your composite key) will be added to each and every entry in each and every non-clustered index

Thus: if you have a composite primary key of 200 bytes in size, and you have a few non-clustered indices on your table, you'd be wasting a lot of memory on your SQL Server - and not just on the (comparatively cheap) disks, but also in SQL Server main memory (which typically isn't quite as cheap).

Besides waste of space, your performance will also lag, since larger indices mean more disk I/O for the same operations.

In general: use a composite primary key on your table only if you never need to reference that table (really never, not even in the future), and if you have no other non-clustered indices on that table.

Answer 2

Business requirements (data integrity requirements) should be the determining factor about what keys to implement. Enforcing uniqueness on one attribute is obviously a different to enforcing it on more than one because duplicates that would be permitted in one case would not be permitted in the other (unless you implement both keys of course).

Note that marc's answer applies only to clustered indexes, not primary keys. They are not the same thing. His answer is also specific to SQL Server.

Answer 3

And how can you possibly be absolutely certain your key "could never be referenced by a FK" ?

Your combination of attributes is indeed unique (otherwise you wouldn't be considering making it a 'primary key').

Your combination of attributes is therefore a valid means of identification for the real-world thing that is described in your table.

Saying that this could never be referenced by a FK is tantamount to saying that "no extra information regarding this type of thing will ever become relevant to the business". How can you possibly know ?

Answer 4

There are two competing philosophies on this issue.

I'm firmly in the camp of using composite primary keys for certain tables, myself.

When I design a database, I use ER modeling to collect information requirements in one place. Every value to be served up by the database is an instance of an attribute, and every attribute describes a subject matter entity or a relationship among two or more subject matter entites. Foreign keys don't go into the analysis phase.

Before starting database design, I decide how each entity will be identified, from the application perspective. These are going to give me my primary keys. Every table that describes an entity will have a simple primary key, the identifier for the entity. Simple relationships (binary, many-to-one) don't need a table of their own. Every table that describes a complex relationship will have a composite primary key made up of the primary keys of the participating entities.

Foreign keys plug in in the obvious way. Well, obvious to me, at least. This provides an initial table design in 3NF, and maybe higher. Table design might be altered by further normalization or by other design patterns incompatible with normalization (so called denormalization). But this is the first cut at table design.

This design practice results in different results as far as performance and data integrity than the prevailing practice. The prevaling practice puts an autonumber column called "id" in as the first column of every table. This column becomes the primary key.

In essence, this practice uses the SQL table structure to mimic the graph model of data, even if it looks like a relational model. The id column is essentially a surrogate for the row's address. The graph model of data has an upside and a downside. More on this if requested.