Every database course, every technical interview and every real schema design runs into the same question: what are the types of keys in DBMS, and how do they differ? The confusing part is not the definitions, it is that every tutorial explains each key with a different example. This guide does it differently: one small STUDENTS table, used for all eight key types, so you can see every key living in the same data. By the comparison table at the end, the whole topic fits on one page of your memory.

Topic	Keys in relational databases (DBMS)
Keys covered	Super, candidate, primary, alternate, foreign, composite, unique, surrogate
Method	One STUDENTS table used for every definition
Best for	Exams, interviews and first real schema designs
Includes	Comparison table, NULL rules, common mistakes

One Table, Every Key

Most tutorials explain each key with a different example, which is exactly why the subject feels harder than it is. This guide uses one small table the whole way through: a university’s STUDENTS table. Every key type below is just a different question asked about this same table. Here it is, three rows and five columns, and by the end of the page you will be able to point at every kind of key in it.

StudentID	RollNo	Email	Name	DeptID
1001	CS-24-001	asha@uni.ac.uk	Asha Patel	D01
1002	CS-24-002	ben@uni.ac.uk	Ben Cole	D01
1003	ME-24-001	ben.c@uni.ac.uk	Ben Cole	D02

Super Key: Any Combination That Identifies A Row

A super key is any set of columns that uniquely identifies a row, with no rule against carrying passengers. In our table, {StudentID} is a super key, but so is {StudentID, Name}, and so is {Email, DeptID}, and so is the entire set of all five columns. If it pins down one row, it qualifies. That generosity is the point: super keys are the raw material, the full list of every combination that could identify a student, before we start trimming the fat. Every other key on this page is a super key with extra conditions attached.

Candidate Key: The Minimal Ones

Strip every unnecessary column out of your super keys and what remains are candidate keys: minimal sets where removing any column breaks uniqueness. In our table there are three: StudentID (the university’s own number), RollNo (unique per student) and Email (no two students share one). {StudentID, Name} is not a candidate key because Name adds nothing, StudentID alone already does the job. Candidate keys are called that because each is a candidate for the job of primary key, and the next section is the interview.

Primary Key: The One You Choose

The primary key is simply the candidate key you promote: one per table, never NULL, never duplicated, and ideally never changing. Our sensible choice is StudentID, emails change when people marry or graduate, roll numbers get restructured, but a system-issued ID stays stable for life. That stability rule is why database designers avoid using things like phone numbers or emails as primary keys even though they are technically unique today. The primary key is a promise to every other table in the database, and promises should be made on things that do not move.

Alternate Key: The Runners-Up

The candidate keys that did not get promoted do not disappear; they become alternate keys. In our table, once StudentID takes the primary key job, RollNo and Email are the alternates: still unique, still enforced, just not the official row identifier. Real systems lean on them constantly, the login screen finds your row by Email, the exam office finds it by RollNo, while the database internally joins everything on StudentID. Alternate keys are why one row can be reached by three different doors without any confusion about which row it is.

Foreign Key: The Link Between Tables

Now the key that makes databases relational. DeptID in our STUDENTS table is a foreign key: it points at the primary key of another table, DEPARTMENTS, and its only job is to make the link. Notice it is not unique in STUDENTS, two students sit in D01, and that is fine; uniqueness is the parent table’s job. What the database enforces is referential integrity: you cannot give a student a DeptID that does not exist in DEPARTMENTS, and you cannot delete a department while students still point at it. Every join you will ever write runs across a foreign key.

DeptID	DeptName
D01	Computer Science
D02	Mechanical Engineering

Composite Key: When One Column Is Not Enough

Sometimes no single column can identify a row and you need two or more together. Look at our third row: there are two students called Ben Cole, so Name alone can never be a key, but {Name, DeptID} happens to be unique in this data. Composite keys are everywhere in link tables, an ENROLMENTS table joining students to courses is naturally keyed on {StudentID, CourseID}, because only the pair is unique. The working rule: use a composite key when the combination is the identity, and be honest that three-and-four-column keys are usually a sign you want a surrogate instead.

Unique Key: The Constraint Version

In practice, unique key usually refers to the UNIQUE constraint you place on a column that must not repeat but is not the primary key. Our Email column is the classic case: enforce uniqueness so no two students register with the same address, while StudentID remains the primary key. The textbook difference is small but examinable: a primary key never accepts NULL, while a unique-constrained column in most databases will accept a NULL (typically one), because NULL is the absence of a value rather than a value. Unique keys are how alternate keys get enforced in real SQL.

Surrogate Key: The Made-Up Number

A surrogate key is an identifier with no real-world meaning, invented purely to be the primary key: an auto-incrementing integer, a UUID, a sequence. Our StudentID is exactly that, the number 1001 says nothing about Asha, which is precisely its virtue, because facts about people change and meaningless numbers do not. The opposite is a natural key, a real-world value like Email pressed into service. The modern default in most production systems is surrogate primary key plus unique constraints on the natural candidates, and now you know the vocabulary to say why.

The Comparison Table

Here is the whole page in one view, using the same STUDENTS table throughout. If you can reproduce this table from memory with your own example, you have this topic beaten for any exam or interview.

Key	Unique?	NULL allowed?	How many per table?	In our example
Super key	Yes	Depends	Many	{StudentID, Name}, {Email}, …
Candidate key	Yes	No	One or more	StudentID, RollNo, Email
Primary key	Yes	Never	Exactly one	StudentID
Alternate key	Yes	No	Zero or more	RollNo, Email
Foreign key	No	Yes (unless restricted)	Zero or more	DeptID
Composite key	Yes (as a set)	No	Situational	{Name, DeptID} in some designs
Unique key	Yes	Usually one NULL	Zero or more	Email as a constraint
Surrogate key	Yes	Never	One, if used	StudentID (auto-generated)

How To Choose Keys In A Real Design

The working sequence designers actually follow: list everything unique (your super keys), reduce them to minimal candidates, promote the most stable one to primary, preferably a surrogate, enforce the rest with unique constraints, and wire tables together with foreign keys. Two habits prevent most disasters: never build a primary key on data a human can change, and always declare your foreign keys instead of just trusting the application code to keep IDs honest. The database will enforce in microseconds what a code review might miss for months.

Common Mistakes That Cost Marks And Sleep

The classics, from exam halls and production incidents alike: calling every unique column a primary key (only one gets the title); using email or phone as the primary key and paying for it the first time a user changes it; forgetting that foreign keys may repeat and may be NULL; writing composite keys where a surrogate would be cleaner; and mixing up candidate and super keys, remember, candidates are minimal, supers are anything that works. Interviewers love the NULL question: primary key, never; unique constraint, usually one allowed. Say it that precisely and you sound like you have shipped databases.

Where You Meet These Keys Outside Exams

This vocabulary is not academic trivia. Every system you touched today, your bank joining accounts to transactions, a booking site joining seats to flights, runs on primary and foreign keys holding the joins together, and modern data-sharing systems like the APIs behind open banking platforms are essentially agreements about whose keys identify what. When a report shows the wrong customer’s data, somewhere a key was designed badly. The eight definitions above are the difference between guessing and knowing which one.

Final Thoughts

Eight keys, one table. Super keys are anything that identifies a row, candidates are the minimal ones, the primary is the promoted candidate, alternates are the runners-up, foreign keys link tables, composites combine columns, unique keys enforce the alternates, and surrogates are the made-up numbers that keep it all stable. Learn it on one example and you will never untangle it again.

Disclaimer: This is an educational guide to standard relational database concepts. Exact NULL and constraint behaviour varies slightly between database systems — always check your own database’s documentation for implementation details.

Frequently Asked Questions

What are the types of keys in DBMS?

Eight matter: super key, candidate key, primary key, alternate key, foreign key, composite key, unique key and surrogate key. Each is defined and shown on one example table above.

What is the difference between a primary key and a unique key?

Both enforce uniqueness, but a table has exactly one primary key which never accepts NULL, while it can have many unique keys and most databases allow a NULL in them.

What is the difference between a candidate key and a super key?

A super key is any column set that identifies a row; a candidate key is a minimal super key — remove any column and it stops being unique.

Can a foreign key have duplicate values?

Yes. Foreign keys repeat naturally — many students can share one DeptID. Uniqueness is enforced in the parent table the foreign key points to.

What is a composite key with an example?

A key made of two or more columns that are only unique together, like {StudentID, CourseID} in an enrolments table, or {Name, DeptID} when names repeat across departments.

Why use a surrogate key instead of a natural key?

Because real-world values like emails change and meaningless generated numbers do not. Stable primary keys keep every foreign key reference in the database valid.