Imagine you walk into a library and ask for a book. The librarian can find it using the book title, the ISBN number, or the barcode on the back cover. Each of these works on its own to identify exactly one book. No two books share the same ISBN. No two books have the same barcode. These are like candidate keys in a database.<\/p>\n\n\n\n
In database management systems, identifying records correctly is one of the most important jobs. If you cannot uniquely identify a row in a table, your entire database becomes unreliable. Candidate keys solve this problem by giving you reliable, unique ways to find any record.<\/p>\n\n\n\n
In this guide, you will learn what candidate key in dbms are, how they work, how they differ from other types of keys, and how to identify them in real database scenarios.<\/p>\n\n\n\n
Quick TL;DR Summary<\/strong><\/p>\n\n\n\n A candidate key is a column, or a group of columns, in a database table that can uniquely identify every row in that table. The word “candidate” means it is eligible or qualified to be chosen as the primary key.<\/p>\n\n\n\n Think of it like a job interview. Several candidates apply for one position. Each candidate is qualified. Only one gets selected for the role. In the same way, a table can have several candidate keys, but only one of them gets selected as the primary key.<\/p>\n\n\n\n Every candidate key must follow two strict rules without any exceptions.<\/p>\n\n\n\n No two rows in the table can have the same value for that key. If two students share the same roll number, that column cannot be a candidate key.<\/p>\n\n\n\n You cannot remove any column from the key and still maintain uniqueness. If a combination of two columns works as a unique identifier, but one column alone also works, then the two-column version is not a candidate key. The single column version is.<\/p>\n\n\n\n Read More: <\/strong>Introduction to Database Keys<\/strong><\/a><\/p>\n\n\n\n Look at this simple student table:<\/p>\n\n\n\n A super key is any set of columns that can uniquely identify a row. A candidate key is a super key with no extra columns in it. Every candidate key is a super key. But not every super key is a candidate key.<\/p>\n\n\n\n Using the student example above, these are all super keys: Student_ID, Email, Phone, (Student_ID + Email), (Student_ID + Phone), (Email + Phone), and (Student_ID + Email + Phone).<\/p>\n\n\n\n But the candidate keys are only: Student_ID, Email, and Phone. The combinations are super keys but not candidate keys because they are not minimal.<\/p>\n\n\n\n The primary key is the one candidate key that the database designer chooses to be the main identifier for a table. There can only be one primary key per table, but there can be multiple candidate keys.<\/p>\n\n\n\n If a table has Student_ID, Email, and Phone as candidate keys, the designer picks one, say Student_ID, as the primary key. The other two, Email and Phone, do not disappear. They become alternate keys.<\/p>\n\n\n\n The primary key also has one additional rule beyond being a candidate key. It cannot contain null values. A candidate key theoretically allows null in some database systems, but a primary key never allows null.<\/p>\n\n\n\n An alternate key is simply a candidate key that was not selected as the primary key. Nothing else changes about it. It is still unique, still minimal, and still a valid way to identify a row. It just was not chosen.<\/p>\n\n\n\n So once you pick Student_ID as the primary key, Email and Phone become alternate keys. They continue to exist and can be used in queries. They just do not carry the “primary” label.<\/p>\n\n\n\n [<\/strong>In-article image 5: <\/strong>The infographic should depict the heading title. Have an illustration depicting the below 5 steps as creative icons representing them]<\/strong><\/p>\n\n\n\n Follow these steps whenever you need to find candidate keys in a table.<\/p>\n\n\n\n Write down every column in the table and think about what each one represents in the real world.<\/p>\n\n\n\n Ask yourself whether any two rows could ever have the same value in this column. If yes, it cannot be a candidate key on its own.<\/p>\n\n\n\n If no single column is unique on its own, start combining two columns and check if the combination is unique.<\/p>\n\n\n\n For every combination you find that is unique, check if you can remove any column and still keep uniqueness. If yes, remove it. Keep trimming until the combination is as small as possible.<\/p>\n\n\n\n Every minimal unique set is a candidate key.<\/p>\n\n\n\n Here, Emp_ID is unique for every employee. Aadhar_No is a government-issued number, unique to every individual. Email is also unique per employee.<\/p>\n\n\n\n Name and Department can repeat across rows, so they cannot be candidate keys.<\/p>\n\n\n\n The candidate keys here are: Emp_ID, Aadhar_No, and Email.<\/p>\n\n\n\n Combinations like (Emp_ID + Aadhar_No) are super keys but not candidate keys because Emp_ID alone already works.<\/p>\n\n\n\n In this table, Order_ID alone is not unique. The same order O001 appears in two rows because a single order can contain multiple products.<\/p>\n\n\n\n Product_ID alone is also not unique. P01 appears in two rows.<\/p>\n\n\n\n But the combination of Order_ID and Product_ID is unique. No two rows share the same combination. And you cannot remove either column from this combination because neither alone gives you uniqueness.<\/p>\n\n\n\n So the candidate key here is: (Order_ID, Product_ID). This is called a composite candidate key.<\/p>\n\n\n\n When a single column cannot uniquely identify a row, you need two or more columns together. This is called a composite key. When that composite key is also minimal, it becomes a composite candidate key.<\/p>\n\n\n\n Composite candidate keys are very common in junction tables, which are tables that connect two other tables together. For example, a table recording which students are enrolled in which courses might have columns like Student_ID and Course_ID. Neither alone is unique, but together they form the composite candidate key.<\/p>\n\n\n\n Just because a column has no duplicates right now does not make it a candidate key. Names might be unique today, but two people with the same name might join the organization next year. You need to think about what the column represents, not just what it contains at the moment.<\/p>\n\n\n\n Many beginners identify unique combinations but forget to check if they are minimal. Always try removing columns from your combination and see if it still works.<\/p>\n\n\n\n If a column might not always have a value, it is a risky choice. A candidate key should ideally be not null for every row.<\/p>\n\n\n\n Every candidate key is a super key, but not every super key is a candidate key. The difference is minimality.<\/p>\n\n\n\n Candidate keys <\/a>are not just a theoretical concept. They have direct practical importance.<\/p>\n\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\nWhat Is a Candidate Key in DBMS?<\/h2>\n\n\n\n
The Two Rules Every Candidate Key Must Follow<\/strong><\/h2>\n\n\n\n
\n
\n
Understanding Uniqueness and Minimality With an Example<\/strong><\/h2>\n\n\n\n
Student_ID<\/strong><\/td> Email<\/strong><\/td> Name<\/strong><\/td> Phone<\/strong><\/td><\/tr> 101<\/td> alice@mail.com<\/td> Alice<\/td> 9876543210<\/td><\/tr> 102<\/td> bob@mail.com<\/td> Bob<\/td> 9876543211<\/td><\/tr> 103<\/td> carol@mail.com<\/td> Carol<\/td> 9876543212<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\nCandidate Key vs Super Key<\/strong><\/h2>\n\n\n\n
Candidate Key vs Primary Key<\/strong><\/h2>\n\n\n\n
Candidate Key vs Alternate Key<\/strong><\/h2>\n\n\n\n
How to Identify Candidate Keys in Any Table<\/strong><\/h2>\n\n\n\n
Step 1: List all the columns. <\/strong><\/h3>\n\n\n\n
Step 2: Check each column for uniqueness. <\/strong><\/h3>\n\n\n\n
Step 3: Check combinations. <\/strong><\/h3>\n\n\n\n
Step 4: Apply minimality. <\/strong><\/h3>\n\n\n\n
Step 5: Collect all minimal unique sets.<\/strong> <\/h3>\n\n\n\n
\n The concept of candidate keys<\/strong> was first formally introduced by Edgar F. Codd<\/strong> in 1970<\/strong> through his paper \u201cA Relational Model of Data for Large Shared Data Banks\u201d<\/strong>.\n
\n This work laid the foundation for primary keys<\/strong>, candidate keys<\/strong>, and database normalization<\/strong>. Nearly every modern database system\u2014from MySQL<\/strong> to PostgreSQL<\/strong> to Oracle<\/strong>\u2014still follows principles defined over 50 years ago<\/strong>.\n<\/div>\n\n\n\nReal-World Example<\/strong><\/h2>\n\n\n\n
\n
Emp_ID<\/strong><\/td> Aadhar_No<\/strong><\/td> Email<\/strong><\/td> Department<\/strong><\/td> Name<\/strong><\/td><\/tr> E001<\/td> 1234-5678-9012<\/td> raj@company.com<\/td> HR<\/td> Raj<\/td><\/tr> E002<\/td> 2345-6789-0123<\/td> priya@company.com<\/td> IT<\/td> Priya<\/td><\/tr> E003<\/td> 3456-7890-1234<\/td> arjun@company.com<\/td> Finance<\/td> Arjun<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \n
Order_ID<\/strong><\/td> Product_ID<\/strong><\/td> Customer_ID<\/strong><\/td> Quantity<\/strong><\/td> Date<\/strong><\/td><\/tr> O001<\/td> P01<\/td> C001<\/td> 2<\/td> 2024-01-01<\/td><\/tr> O001<\/td> P02<\/td> C001<\/td> 1<\/td> 2024-01-01<\/td><\/tr> O002<\/td> P01<\/td> C002<\/td> 3<\/td> 2024-01-02<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Composite Candidate Keys<\/strong><\/h2>\n\n\n\n
\n A table can technically have only one candidate key<\/strong>, which automatically becomes the primary key<\/strong> with no alternatives.\n
\n However, in well-designed real-world databases, most tables include multiple candidate keys<\/strong>. For example, a users table<\/strong> often has a user ID<\/strong>, email address<\/strong>, and phone number<\/strong>\u2014all of which can uniquely identify a record.\n<\/div>\n\n\n\nProperties of Candidate Keys Summarized<\/strong><\/h2>\n\n\n\n
\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\nCommon Mistakes to Avoid<\/strong><\/h2>\n\n\n\n
\n
\n
\n
\n
Why Candidate Keys Matter in Database Design<\/strong><\/h2>\n\n\n\n
\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n