SQL: Primary key generation

[EvilManagedCare]

I'm wondering, can the primary key of a record actually be a derived attribute? Is this considered bad form? Is it even possible? At first thought it seems a risky idea, but I still wonder.

I've been working at data modeling for an upcoming database for a long while today so it's distinctly possible I'm just delirious.

 

[Ken g6]

What you probably want is to define the primary key as multiple columns.

Wikipedia: "In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly defined as NOT NULL."

 

[Emulex]

yah ssms click multiple fields and click set primary key

 

[KentState]

I would avoid composite primary keys if possible. The exceptions are typically cross-reference tables which relates two distinct concepts together.

 

[beginner99]

basically there are 2 school, some say composite key is better others say surrogate key (=autogenerated integer) is better.
It depends...both have up and downsides.

One possible downside of surrogate key is when inserting data into multiple joined tables you need to be able to retrieve the auto-generated key. This depends heavily on the used technology (stored procedure,.net, java,...) and not all databases handle this in the same way, especially Oracle.
If you use a "natural key", eg. data you already have present before inserting, you don't' need to retrieve the generated key. I'm not sure but I guess that can improve performance a lot if you do many inserts.

 

[Jeraden]

It's perfectly fine to have a multi-field primary key. However, whether that is good design or not really depends on the function of the table. If it's a table that is going to be linked to via foreign keys in other tables, it's probably not a good idea. But if it's more of a stand-alone table, where you'd be accessing it by the multiple columns anyways, then it's fine, as having a sequential primary key would be a waste really since you'd never need it, and never be storing that id in other tables.

 

[the182guy]

It's fine if it meets your requirements but don't use a composite key unless you have a reason to.

 

[EvilManagedCare]

Per jeraden:

"...But if it's more of a stand-alone table, where you'd be accessing it by the multiple columns anyways, then it's fine, as having a sequential primary key would be a waste really since you'd never need it, and never be storing that id in other tables."

This best matches the situation of the table in question. It is unlikely to ever be referenced by another. I will keep the composite key. Thanks for the input everyone.