There are many different keys in SQL Server that are important to understand.
You might have heard the term “Candidate Key” before, but maybe you aren’t exactly sure what that means. Maybe you know a thing or two about Primary Keys and Foreign Keys, but you’re not sure what the term “Candidate Key” is referring to.
In this tutorial, we’ll discuss the definition and examples of what a Candidate Key is in SQL Server.
We’ll discuss the following topics:
- What is a Candidate Key?
- An example of a Candidate Key.
- How should I decide which Candidate Key to use as my Primary Key?
- Understanding Alternate Keys.
- Tips and tricks.
Let’s begin:
1. What is a Candidate Key?
A Candidate Key is a column or group of columns we can use to uniquely identity a row in a table. There can be multiple Candidate Keys in a table, but we will pick just one of them to serve as the Primary Key of the table.
When looking at a table, you might recognize that there are several columns that could serve as the Primary Key column if you wanted them to. In other words, there are several worthy candidates that would make an effective Primary Key.
It’s important to know that Candidate Keys are completely theoretical. By that, I mean there is no way in SQL Server to assign a column a Candidate Key property.
This is different from a Primary Key, for example. If you want to set a column as the Primary Key, it’s simple to do in a CREATE TABLE statement:
CREATE TABLE Employees ( EmplID INT PRIMARY KEY, FirstName VARCHAR(20), LastName VARCHAR(20) )
There is not a similar thing to identity a column as a Candidate Key.
For example, this isn’t a thing in SQL Server:
CREATE TABLE Employees ( EmplID INT CANDIDATE KEY, FirstName VARCHAR(20), LastName VARCHAR(20) )
This will give you error messages if you try to run it:
So understand that Candidate Keys are an abstract, theoretical thing in SQL Server. It is simply you and your team recognizing that a column (or group of columns) could be the Primary Key. But again, you will eventually pick one to actually serve as the Primary Key.
2. An example of a Candidate Key.
Let’s looks at some simple examples of Candidate Keys in a table. Let’s look at this Employees table:
We’ll break down each column:
- EmplID – Contains an INT value that is populated automatically by the IDENTITY property.
- FirstName, MiddleName, LastName – Obviously is the first, middle, and last name of each employee
- Email – The unique email address for each employee. Notice it’s in the format <FirstName>.<LastName>@skynet.com
- EmplCode – This is a unique code we assign to each employee for payroll purposes.
- SSN – The social security number of each employee for tax purposes.
Folks, there are several Candidate Keys in this table! I’ll point them all out:
1. EmplID
This will auto-increment on every insert into the table (thanks to the IDENTITY property). As long as we don’t mess with it, values in this column ought to be unique, and therefore, would make a good Primary Key.
Take a look at the full tutorial on the IDENTITY property to learn more:
IDENTITY Column in SQL Server: Everything you need to know
2. The Combination of FirstName, MiddleName, and LastName
What are the odds that two or more people in our company will have exactly the same first, middle, and last name?
Not very likely!
Maybe in the context of the entire population of the world it’s more likely, but we’re only looking at people in our company. Remember, a Primary Key could be composed of more than one column. It’s perfectly possible to say our Primary Key is the combination of first, middle, and last name.
3. Email
Again, what are the odds that two or more people will even have exactly same first and last name? Remember that a person’s email address is basically <FirstName>.<LastName>@skynet.com. This is another worthy candidate that could serve as our Primary Key.
4. EmplCode
In our company, we wanted to use a unique code for each employee for payroll purposes. The code is simply their initials and a number.
It’s unique because of the number at the end. For example, Michael Douglas Booth and Maggie Denise Boyle have the same initials (MDB). To distinguish them in the EmplCode column, we gave each of them a different number at the end. So Michael has a code of MDB-001 and Maggie has a code of MDB-002. If someone else comes along with the initials MDB, we’ll give them a code of MDB-003.
Get it?
5. SSN
Finally, the SSN. We need to store a person’s social security number for tax purposes. Every person alive has a unique SSN thanks to good ole’ Uncle Sam. Since it’s guaranteed to be unique, this could also be used as a Primary Key.
The choice is yours!
So it looks like there is a total of 5 Candidate Keys in this table. There are potentially 5 ways to uniquely identity a row in the Employees table.
In other words, there are 5 worthy candidates to serve as our Primary Key.
Now the challenge becomes “Which one do I pick?“. This leads us to our next point.
3. How should I decide which Candidate Key to use as my Primary Key?
The decision of picking a Primary Key is not a decision you should take lightly. There are several things you need to consider.
For example, a Primary Key column must be set to NOT NULL. Do we think any of our Candidate Keys could ever contain a NULL value?
Possibly. I’m thinking about the MiddleName column, and how some people simply don’t have a middle name. NULL would be a perfectly valid value in that case.
Also, you need to think about the size of the data in your Primary Key column, and it’s likelihood to change. Remember, a clustered index will automatically be created on the column(s) you specify to be the Primary Key. A good clustered index key is usually small in size, and is not likely to change.
Take a look at the full tutorial to learn more about what makes a good clustered index key:
SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner
In this case, I would probably pick the EmplID column to be our Primary Key. It uses an INT data type which is relatively small, and the IDENTITY property takes care of entering values for us. As long as I don’t mess with it, values in the column ought to be unique (and if I do decide to make it the Primary Key, the Primary Key constraint will enforce uniqueness).
Honestly, the topic of “What makes a good Primary Key?” could be it’s own tutorial…which I have.
Check out the full beginner-friendly tutorial on the topic of Primary Keys where you can learn more about what makes a good Primary Key for a table.
SQL Server Primary Key: How it works and 6 rules you need to know
4. Understanding Alternate Keys.
The last thing we need to talk about is another term you might hear during your SQL travels. It is “Alternate Keys“.
Once you finally pick a column to be your Primary Key column, all those other candidate columns can be referred to as Alternate Keys. Those columns can serve as a great alternate way to uniquely identify a row in a table.
Again, there is no Alternate Key property in SQL Server. It’s another theoretical thing.
5. Tips and tricks.
Here is a list of tips and tricks you should know about Candidate Keys:
Tip # 1: It’s good to know what columns are Candidate Keys when writing queries
When we’re trying to uniquely identify rows in a table when writing a query, it’s helpful to know other columns you could use besides the Primary Key column.
Sometimes, you simply don’t know the Primary Key value of a row you’re looking to isolate, or maybe you just don’t feel like looking it up.
In that case, it’s good to know that you can use other means to get a unique row. If you don’t know the Primary Key value, but you do know a Candidate Key value, you can be confident that if you write a query using the Candidate Key column in a WHERE clause, it will return just the one row.
Tip # 2: Remember, the decision of which Candidate Key you choose to be your Primary Key has a lot to do with what makes a good clustered index key
I have a great FREE resource you can download to understand the basics of clustered indexes. A proper understanding of indexes is absolutely essential if you are looking to enter the field of database development and administration. You won’t regret owning this guide! Download it today!
FREE EBook on SQL Server Indexes!
Also, check out my full beginner-friendly tutorial on Clustered Indexes:
SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner
Next Steps:
As you have learned, Candidate Keys and Primary Keys are closely related in SQL Server. If you want to learn more about Primary Keys specifically, you should check out the full tutorial on the topic, found here:
SQL Server Primary Key: How it works and 6 rules you need to know
While you’re at it, you should also learn about Foreign Keys in SQL Server. They are a great tool to enforce referential integrity. Check it out:
SQL Server Foreign Key: Everything you need to know
I also created a FREE GUIDE that gives you a quick summary of the most important rules you need to know when working with Primary Keys in SQL Server. This would be a great resource for you to reference when creating tables in SQL Server. Make sure you download it today!:
FREE EBook on SQL Server Constraints!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
