Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers

up vote 9 down vote accepted

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.

share|improve this answer
1  
I usually tell people they should use a surrogate key unless they want to guarantee fragmented indexes and poor performance. There are always exceptions but very, very few in this case. –  AndrewSQL Jan 21 '11 at 14:34
add comment

It is usually recommended that you have a surrogate key in such situations, so foreign keys in other tables (and any record references that may be stored externally, such as if they are carried on query strings where a http(s) request refers to one of the records) have something to refer to that will not change if the data in the row changes. If you do this then that would be your primary key.

If you don't add such a surrogate key, then given how you describe the data being access having all four columns as the primary key would not be a disadvantage. If you make the key the clustered index for the table it will help such requests as there will be one level in the b-tree on disk to go down to find the data for a given row.

share|improve this answer
add comment

Composite keys as primary keys also run into index size issues that can affect disk usage, io speeds, and backups. You might want to review Kimberly Tripp's posts about primary keys and clustered indexes here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-again!.aspx

I too would suggest a surrogate key in this case instead of a natural one.

share|improve this answer
add comment

If you have a table representing a many-to-many relationship which has just 2 columns, it seems reasonable.

Cf. this SO question

But I admit, that I add Surrogate Keys even in those cases.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.