Introduction
Not long ago, I had an interesting and extended debate with one of my friends regarding which column should be primary key in a table. The debate instigated an in-depth discussion about candidate keys and primary keys. My present article revolves around the two types of keys.
Let us first try to grasp the definition of the two keys.
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.
One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.
An Example to Understand Keys
Let us look at an example where we have multiple Candidate Keys, from which we will select an appropriate Primary Key.
Given below is an example of a table having three columns that can qualify as single column Candidate Key, and on combining more than one column the number of possible Candidate Keys touches seven. A point to remember here is that only one column can be selected as Primary Key. The decision of Primary Key selection from possible combinations of Candidate Key is often very perplexing but very imperative!
On running the following script it will always give 504 rows in all the options. This proves that they are all unique in database and meet the criteria of a Primary Key.
Run the following script to verify if all the tables have unique values or not.
USE AdventureWorks
GO
SELECT *
FROM Production.Product
GO
SELECTÂ DISTINCT ProductID
FROM Production.Product
GO
SELECTÂ DISTINCT Name
FROM Production.Product
GO
SELECTÂ DISTINCT ProductNumber
FROM Production.Product
GO All of the above queries will return the same number of records; hence, they all qualify as Candidate Keys. In other words, they are the candidates for Primary Key. There are few points to consider while turning any Candidate Key into a Primary Key.
Select a key that does not contain NULL
It may be possible that there are Candidate Keys that presently do not contain value (not null) but technically they can contain null. In this case, they will not qualify for Primary Key. In the following table structure, we can see that even though column [name] does not have any NULL value it does not qualify as it has the potential to contain NULL value in future.
CREATEÂ TABLE [Production].[Product](
[ProductID]Â [int] IDENTITY(1,1)Â NOTÂ NULL,
[Name]Â [dbo].[Name] NULL,
[ProductNumber]Â [nvarchar](25)Â NOTÂ NULL,
[Manufacturer]Â [nvarchar](25)Â NOTÂ NULL
)Select a key that is unique and does not repeat
It may be possible that Candidate Keys that are unique at this moment may contain duplicate value. These kinds of Candidate Keys do not qualify for Primary Key. Let us understand this scenario by looking into the example given above. It is absolutely possible that two Manufacturers can create products with the same name; the resulting name will be a duplicate and only the name of the Manufacturer will differ in the table. This disqualifies Name in the table to be a Primary Key.
Make sure that Primary Key does not keep changing
This is not a hard and fast rule but rather a general recommendation: Primary Key values should not keep changing. It is quite convenient for a database if Primary Key is static. Primary Keys are referenced in numerous places in the database, from Index to Foreign Keys. If they keep changing then they can adversely affect database integrity, data statistics as well as internal of Indexes.
Selection of Primary Key
Let us examine our case by applying the above three rules to the table and decide on the appropriate candidate for Primary Key. Name can contain NULL so it disqualifies as per Rule 1 and Rule 2. Product Number can be duplicated for different Manufacturers so it disqualifies as per Rule 2. ProductID is Identity and Identity column cannot be modified. So, in this case ProductID qualifies as Primary Key.
Please note that many database experts suggest that it is not a good practice to make Identity Column as Primary Key. The reason behind this suggestion is that many times Identity Column that has been assigned as Primary Key does not play any role in database. There is no use of this Primary Key in both application and in T-SQL. Besides, this Primary Key may not be used in Joins. It is a known fact that when there is JOIN on Primary Key or when Primary Key is used in the WHERE condition it usually gives better performance than non primary key columns. This argument is absolutely valid and one must make sure not to use such Identity Column. However, our example presents a different case. Here, although ProductID is Identity Column it uniquely defines the row and the same column will be used as foreign key in other tables. If a key is used in any other table as foreign key it is likely that it will be used in joins.
Quick Note on Other Kinds of Keys
The above paragraph evokes another question – what is a foreign key? A foreign key in a database table is a key from another table that refers to the primary key in the table being used. A primary key can be referred by multiple foreign keys from other tables. It is not required for a primary key to be the reference of any foreign keys. The interesting part is that a foreign key can refer back to the same table but to a different column. This kind of foreign key is known as “self-referencing foreign key”.
Summary
A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules – 1) Not Null, 2) Unique Value in Table and 3) Static – are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind.
Reference: Pinal Dave (https://blog.sqlauthority.com), DNS
55 Comments. Leave new
Thanks alot today i understand difference b/w these keys.
Thanks a lot I understand difference key’s !
hi sir
i have created some tables using primary key and foreign key .when i was deleting records for that table which id can i taken . either primary key or foreign key id . which one is best one ? please tell me with soome examples
Thanks alot today i understand difference b/w these keys.
Very nice explanation
I have recently had a debate with a colleague about candidate keys and primary keys, was interesting to read your thoughts!
You look kind of asian in your picture
Thank you for your insights into deciding on what best to use as a tables PK. Here are my 2 cents on the topic for whatever they’re worth. While I do understand the argument for using candidate keys as opposed to surrogate / generated keys, somewhere during my fairly lengthy time of designing databases I have become a die hard advocate of the integer based generated or INCREMENT approach. There are several reasons for this, number one on the list being that internally generated keys are wholly owned and therefore 100% in the control of the DBMS. Candidates are almost always generated by an outside source and can be changed by them as radically and as often as they wish. A prime example of this occured as a biproduct of the merger of 2 large manufacturers. The decision was made to create all new product ID’s to accomodate the newly formed company, and a wholesaler client of mine that kept a large inventory of one of the company’s products was forced to deal with obsolete PK’s on many millions of rows. Ultimately they decided to treat the outdated IDs as internal IDs and create a lookup table to cross-reference the new IDs, and wishing they had generated their own IDs from the begining. A couple of modified views and problem solved. I also find that IDs often have alpha content that holds some attachment or meaning to whoever chose them. Integers rarely have any meaning other than to uniquely identify something and so in my experience are far less likely to change than alpha-numerics. There are also no issues with single or double byte character sets, etc. as far as PKs and FKs are concerned. Of course if everyone used generated IDs there would never have been a debate over which candidate to use on your table and this discussion would never have taken place ;)
very nice information about sql keys
thanks for share
all non primary keys in candidate keys is ______________called????
alternate keys.
More than one primary key in a table is called___________ .
A table can have only one and only one primary key. however the primary key in a table can be composite with multiple attributes.
The table can have as many as candidate keys, all other candidate keys except the primary key is called alternate keys.
ur qstn is unambigious
agree with Teacher
Hi,
For a given table, if I count the keys (other then the primary key) which are having NOT NULL and UNIQUE, both the constraints, will that give me count of secondary keys for that table?
If not, how to get count of secondary keys for a table by using SQL query?
Can candidate key accept null value?
Yes it can………..
I want major difference between candidate key and primary,i did not found any difference between those two. Any one clarify my doubt please.
The explanation in very clear, i like it.
If I would be designing primary key here. I would choose column Name and Product number as composite primary key. Reason Searches are most important and reads are more then writes. I feel product id is meaningless and waste of your clustered index. If you test read performance in a test environment. Where clause would always be on Name of product therefore gives you better performance. Else you have to search for product name from the FK table by joining it to product table on useless id column only to find the name. Pinal could you give me reasoning why my way of looking at it is not right ?
nyc article keep up
That was a nice explanation to differentiate candidate key from primary key. Thank you.
these 2 statements are conflicting
1) Each Candidate Key can qualify as Primary Key
2) Only one Candidate Key can be Primary Key.
Hi Srinivas,
They are not conflicting.
Read again – Each Candidate Key can be qualify as a Primary key but out of that only one can become Primary key.
I hope this helps.
can yu please create a table with candidate key?