Identity column Vs Primary Key [closed]

Question

How can we decide on to go with Identity column or Primary Key?

Answer 1

From experience rather than theory I have discovered that it is important to use keys that are not shared by the user as "user data". That is, user data, and keys should be different fields.

We had a database which used project codes. The project codes were unique, never null, and had a business rule that once assigned would never be changed. That makes it a candidate key.

However, if users see the project codes, look up file by project code and so forth, it's their data, and they will want to change business rules around the project code.

Our project codes were customer id followed by a dash and a sequence number to make it unique. If a proposed project for one customer id ended up with a contract for a different customer id, the users wanted to change the project code. If the user entered it with the wrong customer id, they wanted to change the project code. Changing a primary key, and all the foreign keys referencing it is problematic.

That doesn't (seem to) answer the question How can we decide on to go with Identity column or Primary Key?

However, if you don't use customer data for a primary key, (as I have just argued you should not) then the primary key will need to be auto-generated, because you cannot not expect the user to enter it.

Answer 2

You should always use a Primary Key it just makes your Database scalable. Use a GUID or a UUID or anything else that unique.

Answer 3

you use an Identity column when you need an auto increment. Period! Simple as that. Usually the identity column is a good candidate for a primary key.

Primary key is just a concept. Whats important behind the primary key is that it creates a CLUSTERED INDEX on the column so, in theory, you dont need the "Primary key" if you have a table with an unique clustered index.

I suggest you google and read about these two concepts, which are very importatn to understand

Answer 4

The two concepts are not mutually exclusive. All combinations are possible:

• An identity column that is also a primary key,
• An identity column that is not a primary key,
• A primary key column that is not an identity,
• A column that is neither primary key nor identity

Note that an identity column is often used as a primary key as it is guaranteed to be unique, and it is usually in addition to the required schema fields, so it will not ahve to change if the schema changes.