Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need to comprise all columns. Queries against the table will almost always be to pull back a single record, i.e. all columns will be filtered in the query.

Since every column will need to be searched, does having a primary key benefit me at all (besides enforcing uniqueness of records)?

In your case these fields are natural key.


Surrogate Key:


Surrogate keys are keys that have no
“business” meaning and are solely used
to identify a record in the table.
Such keys are either database
generated (example: Identity in SQL
Server, Sequence in Oracle,
Sequence/Identity in DB2 UDB etc.) or
system generated values (like
generated via a table in the schema).


Natural Key:


Keys are natural if the attribute it
represents is used for identification
independently of the database schema.
What this basically means is that the
keys are natural if people use them
example: Invoice-Numbers, Tax-Ids, SSN
etc.

Surrogate Keys vs Natural Keys for Primary Key

I prefer to add surrogate key to separate business and database model management.
Other question is using clustered and nonclustered index on primary key..
If you table changes (non static table, it has high intensive inserts or update), you will get problem with performance in case of using clustered index on non-monotonic increased key.

StackExchange Database Administrators Q#760, answer score: 12