Database Primary Key's -> An identity field AND a name field?

Question

Hi folks,

all my tables have an Id field of some type (UserId, PostId, FooId, etc). I usually make this a Primary Key.

A table I have is called Countries. It has

CountryId SMALLINT
Name VARCHAR(100)  -- Yes, english country names only, in this column.
AndSomeOtherFields.

Now, I know the Name has to be unique. All country names are unique. Is it good/bad/ru-roh if i make the PrimaryKey == CountryId ASC and Name ASC ?

If it's good, can someone explain why it's better, than just the Id being the PK? Is it just that it ensures data integrity (eg. no two country names existing in the table). If it's bad .. why?

thanks kindly.

Answer 1

Making the primary key both CountryId and Name does not ensure the names are unique. It just ensures that each CountryId-Name pair is unique, and obviously CountryId is already unique, being an "ID". So you could still have, for example, 1-US and 19-US, as the pairs are unique.

The only reason to make them both the primary key is if you will be frequently executing queries where both CountryId and Name are used in the Where clause. The primary key by default creates a clustered index, which physically sorts the table, so it makes lookups for rows against those predicates very fast.

Another important point to raise is that in your particular example, you are storing a list of countries which is a) very short and b) doesn't change much. Lookups against this table are going to be extremely fast no matter what you do. Even if SQL Server has to do a full table scan every time, you probably won't even notice. You don't have to worry about page fragmentation. You could just skip the ID column and use the Name as a primary key.

Or, if you want to keep an ID but also enforce uniqueness of just the country names, you can put a Unique Constraint on the Name column.

It is difficult to cover the issue of primary keys, clustered indexes, and indexes in general, in too much depth in a single answer. Here are a few good resources to get started:

• When should primary keys be used?
• An Introduction to Clustered and Non-Clustered Index Data Structures

Answer 2

Having the name become the PK is not always the best solution, I believe CountryId is enough as a PK for your table, however if Name is a field which you will be using alot to query with ie: selects,joins , you should index this field this way a query filtering by this field will improve it's speed alot

good luck :)

Answer 3

The only thing I can think of is pretty obvious: your index will be a little bigger. Having said so, it's not such big a deal since your table will be only storing countries. But, why would you like such an index? If you sort by CountryId, then sorting it by Name as second field is pointless. You will always get the same order.

One thing that is really a bad idea is to have foreign keys pointing to a large primary key, so, make sure that if you use that primary key, your foreign keys pointing to Countries still use only CountryId column.

Answer 4

Country names have been known to change without otherwise altering the country's identity. That suggests that the name should not be part of the PK.

Answer 5

If you need to enforce uniqueness, use a constraint.

Answer 6

Create a unique index on the column Name.

Answer 7

Duplicate

• http://stackoverflow.com/questions/166750/should-i-have-a-dedicated-primary-key-field
• http://stackoverflow.com/questions/695325/should-i-use-natural-identity-columns-without-guid

And that's just the ones I could see on the right, this question has been asked quite a few times.