Keys in Database: Primary, Foreign, Candidate, Composite and Super Key

If tables are the building blocks of a relational database, keys are the rules that make those tables reliable, connected, and unique. Without them, a database is barely more than a spreadsheet. This database keys tutorial covers primary key in sql, foreign key in sql, candidate keys, super keys, and composite key sql examples, the exact concepts interviewers probe to check whether you actually understand the relational model.

What are Database Keys?

A database key is one or more columns used to uniquely identify rows in a table or to connect one table to another. The primary key uniquely identifies each row and can never be NULL; the foreign key in sql references another table's primary key to create relationships and enforce referential integrity sql rules. Candidate keys, super keys, and composite keys describe the other roles a column or column combination can play in that identification system.

What You'll Learn

• Define and distinguish primary key, foreign key, candidate key, and super key.
• Understand why each key type is needed in relational database design.
• Implement primary and foreign keys correctly in MySQL CREATE TABLE statements.
• Explain referential integrity and how foreign keys enforce it.

Key Terms to Know

• Primary Key: A column or column combination that uniquely identifies every row; cannot be NULL, exactly one per table.
• Foreign Key: A column referencing another table's primary key, creating a relationship and enforcing referential integrity.
• Candidate Key: Any minimal column combination that could serve as the primary key; a table can have several.
• Super Key: Any column combination that uniquely identifies rows, including non-minimal ones with extra columns.
• Composite Key: A primary or foreign key made of two or more columns, used when no single column is unique enough.

Primary Key, Candidate Key, and Super Key: How They Relate

Candidate keys are the full pool of columns that could become the primary key. In a students table, both student_id and email might be unique and never null, making both candidate keys. Once you pick student_id as the primary key, email becomes an alternate key, still unique, just not the one chosen. Super keys are every unique identifying set you can form, including unnecessarily large ones like student_id plus email together.

This is the relationship to remember for the candidate key vs primary key and super key vs candidate key questions that show up constantly in interviews: every primary key is a candidate key, every candidate key is a super key, but the reverse isn't true.

Foreign Key in SQL and Referential Integrity

Foreign keys are what make relational databases genuinely relational. In an orders table, customer_id is a foreign key referencing customer_id in the customers table, meaning every order must point to a customer that actually exists. Try inserting an order with a customer_id that doesn't exist, and MySQL rejects it outright, that rejection is referential integrity sql in action.

Foreign keys also define what happens when a referenced row changes. ON DELETE CASCADE deletes related child rows automatically; ON DELETE SET NULL clears the foreign key instead; ON DELETE RESTRICT, the default, blocks the deletion while related rows still exist.

Composite Keys: When One Column Isn't Enough

Composite key sql design is needed exactly when no single column is unique on its own, but a combination of columns is. In an enrollments table, neither user_id alone nor course_id alone is unique, since one user enrolls in many courses and one course has many users, but the pair (user_id, course_id) together is unique. Declaring PRIMARY KEY (user_id, course_id) prevents the same user from enrolling in the same course twice.

Visual Summary

Picture two tables connected by an arrow. CUSTOMERS has customer_id as its primary key and email as a candidate key. ORDERS has order_id as its own primary key and customer_id as a foreign key, with an arrow pointing to CUSTOMERS.customer_id. Inserting an order with a customer_id that doesn't exist in CUSTOMERS gets rejected — that's referential integrity in action.

Database Key Types at a Glance

Key Type	Uniqueness Required?	NULL Allowed?	Purpose
Primary Key	Yes	No	Uniquely identify each row
Candidate Key	Yes	No (minimal)	All possible primary key choices
Super Key	Yes (any set)	May vary	Any unique identifying set
Foreign Key	References unique	Usually allowed	Connect to another table
Composite Key	Yes (combined)	No (for PK)	Multi-column unique identifier
Surrogate Key	Yes	No	System-generated artificial ID

SQL Example

-- Primary key (surrogate) + candidate key (email)
CREATE TABLE customers (
  customer_id   INT          PRIMARY KEY AUTO_INCREMENT,
  email         VARCHAR(150) UNIQUE NOT NULL,
  customer_name VARCHAR(100) NOT NULL
);

-- Foreign key connecting orders to customers
CREATE TABLE orders (
  order_id    INT  PRIMARY KEY AUTO_INCREMENT,
  customer_id INT  NOT NULL,
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- Composite primary key: a student can't enroll in the same course twice
CREATE TABLE course_enrollments (
  student_id  INT  NOT NULL,
  course_id   INT  NOT NULL,
  enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES customers(customer_id)
);

In the customers table, customer_id is a surrogate primary key, while email is a unique NOT NULL column, making it a candidate key (and an alternate key, since it wasn't chosen as the primary key). The orders table's customer_id is a foreign key enforcing referential integrity, and in course_enrollments, the combination of student_id and course_id forms a composite primary key that blocks duplicate enrollments.

Real-World Examples

• Banking systems use account_id as a surrogate primary key for accounts and a PAN card number as an alternate key, with transactions using account_id as a foreign key to a valid account.
• Train booking systems use PNR numbers as natural primary keys for bookings, with passenger IDs as foreign keys connecting bookings to passenger records.
• E-learning platforms like Coursera use user_id and course_id as a composite primary key in enrollment tables, ensuring a user can't enroll in the same course twice.
• Hospital systems use a patient's unique medical record number as an alternate key alongside a surrogate patient_id, since the medical record number is the real-world identifier doctors actually reference.

Best Practices and Pro Tips

• Default to a surrogate AUTO_INCREMENT primary key for almost every table, and reserve natural keys like a PAN number or email for UNIQUE constraints instead. Natural data has a habit of needing to change later, and primary keys ideally shouldn't.
• When you're unsure whether something should be RESTRICT, CASCADE, or SET NULL on delete, default to RESTRICT. It's the safest failure mode, since it forces you to handle the deletion explicitly instead of silently cascading damage through related tables.
• Always index the foreign key column itself, not just the primary key it references. InnoDB does this automatically for declared foreign keys, but if you're filtering or joining on a column that isn't formally a foreign key, add that index by hand.

Common Mistakes to Avoid

• Confusing candidate key with super key — a candidate key is minimal, a super key can include extra, redundant columns.
• Saying a table can have multiple primary keys — a table has exactly one primary key, which may span multiple columns.
• Creating foreign keys without a REFERENCES clause pointing to an actual primary key or unique column.

Interview Questions

Q1. What is a primary key and what are its properties?

A primary key is a column or column combination that uniquely identifies every row in a table. Its values must be unique, it cannot be NULL, a table has exactly one (though it may span multiple columns), and it's typically indexed automatically for fast lookups.

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

A candidate key is the minimal set of attributes that uniquely identifies a row; removing any attribute would break that uniqueness. A super key is any set of attributes that uniquely identifies rows, including non-minimal sets with extra columns. All candidate keys are super keys, but not all super keys are candidate keys.

Q3. What is referential integrity and how do foreign keys enforce it?

Referential integrity means every foreign key value must match an existing primary key value in the referenced table, or be NULL if allowed. Foreign key constraints enforce this by rejecting inserts or updates that would create an orphan reference.

Practice MCQs

1. How many primary keys can a single table have in a relational database?

1. Zero
2. One
3. Two
4. As many as needed

Answer: B. One

Explanation: A table can have exactly one primary key, though that key may consist of multiple columns (composite key).

2. Which of the following key types enforces referential integrity between two tables?

1. Primary key
2. Super key
3. Foreign key
4. Candidate key

Answer: C. Foreign key

Explanation: A foreign key column references the primary key of another table, and its constraint prevents orphan rows that reference non-existent records.

3. Which ON DELETE option for a foreign key automatically removes all child rows when the parent row is deleted?

1. RESTRICT
2. SET NULL
3. NO ACTION
4. CASCADE

Answer: D. CASCADE

Explanation: ON DELETE CASCADE automatically deletes all rows in the child table that reference the deleted parent row.

Quick Revision Points

• Primary key: unique, not null, one per table, can be composite.
• Candidate key: minimal unique non-null column set; all candidate keys are super keys.
• Foreign key: references another table's primary key; enforces referential integrity.
• ON DELETE options: RESTRICT (default), CASCADE, SET NULL, NO ACTION.

Conclusion

• Keys are what make relational databases genuinely relational — without them, tables are just independent storage containers.
• Every JOIN you write later depends on the primary and foreign key relationships defined here.
• The hierarchy super key → candidate key → primary key explains the theory behind real table design decisions.

Database keys are the foundation of relational integrity. The primary key in sql uniquely identifies every row; the foreign key in sql connects tables and enforces referential integrity sql rules; candidate and super keys describe every possible identifying column set, and composite keys round out the picture for cases where a single column isn't enough. Together, these key types are what enable reliable JOINs and prevent duplicate or orphaned records in any well-designed schema.