SQL Keys

SQL Keys Interview with follow-up questions

1. What is a primary key in SQL and why is it important?

A primary key is a column (or combination of columns) that uniquely identifies every row in a table. It is the most important constraint in relational database design.

Properties enforced by the PRIMARY KEY constraint:

  1. Uniqueness — no two rows can share the same primary key value
  2. NOT NULL — primary key columns cannot contain NULL
  3. Single per table — each table can have only one primary key (though it may span multiple columns — a composite primary key)
-- Single-column primary key
CREATE TABLE users (
    user_id   INT          PRIMARY KEY,
    email     VARCHAR(200) NOT NULL UNIQUE,
    username  VARCHAR(50)  NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Why primary keys matter:

  • Row identification: The database (and application code) can pinpoint any specific row
  • Index: The DBMS automatically creates a unique index on the primary key, making lookups O(log n)
  • Foreign key target: Other tables reference this table via foreign keys pointing to the primary key
  • Clustered index (SQL Server, MySQL/InnoDB): The primary key determines the physical storage order of rows

Interview follow-up: What is the difference between a primary key and a unique key? A unique key allows NULL (one NULL per column in most databases) and a table can have multiple unique keys, but only one primary key.

↑ Back to top

Follow-up 1

Can a table have more than one primary key?

No, a table cannot have more than one primary key. The primary key constraint is used to uniquely identify each row in a table, and having multiple primary keys would contradict this purpose. However, a primary key can consist of multiple columns, known as a composite primary key.

Follow-up 2

What happens if the primary key has null values?

A primary key cannot have null values. The primary key constraint requires that the key values be unique and not null. If a primary key column allows null values, it would not be able to uniquely identify each row in the table, which violates the primary key constraint.

Follow-up 3

How does a primary key differ from a unique key?

Both primary key and unique key constraints are used to enforce uniqueness in a table. However, there are some differences between them:

  • A table can have only one primary key, but it can have multiple unique keys.
  • The primary key constraint automatically creates a clustered index on the key column(s), whereas the unique key constraint does not.
  • The primary key constraint does not allow null values, whereas the unique key constraint allows one null value (in case of a single-column unique key) or multiple null values (in case of a composite unique key).

2. Can you explain what a foreign key is and provide an example of its use?

A foreign key is a column (or set of columns) in one table that references the primary key (or unique key) of another table. It enforces referential integrity — ensuring that relationships between tables remain consistent.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE,
    total       DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, you cannot insert an order with a customer_id that does not exist in the customers table. And you cannot delete a customer who has existing orders (unless a cascade rule is defined).

Referential actions (what happens when the parent row is updated or deleted):

Action Behavior
RESTRICT / NO ACTION Block the parent delete/update if child rows exist (default)
CASCADE Automatically delete/update child rows when parent changes
SET NULL Set the foreign key column to NULL in child rows
SET DEFAULT Set the foreign key column to its default value 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

Interview follow-up: Foreign keys can be disabled temporarily for bulk data loads (to avoid constraint checking on every insert), then re-enabled and validated afterward. In PostgreSQL: SET session_replication_role = 'replica';. In MySQL: SET FOREIGN_KEY_CHECKS = 0;.

↑ Back to top

Follow-up 1

What is referential integrity and how does a foreign key help maintain it?

Referential integrity is a database concept that ensures the relationships between tables are maintained correctly. It ensures that the values in the foreign key column(s) of a table match the values in the primary key column(s) of the referenced table.

A foreign key helps maintain referential integrity by enforcing the following rules:

  1. Insert Rule: It ensures that a value cannot be inserted into the foreign key column(s) unless it matches a value in the primary key column(s) of the referenced table.

  2. Update Rule: It ensures that if a value in the primary key column(s) of the referenced table is updated, the corresponding foreign key values in other tables are also updated.

  3. Delete Rule: It ensures that if a record in the referenced table is deleted, the corresponding records in other tables that reference it through foreign keys are also deleted or updated accordingly.

Follow-up 2

Can a foreign key accept null values?

Yes, a foreign key can accept null values. By default, a foreign key column allows null values, which means it can have missing or unknown values. However, it is generally recommended to avoid using null values in foreign key columns as they can lead to data integrity issues. It is best to define the foreign key column as NOT NULL to enforce the relationship between tables.

Follow-up 3

What happens if we try to delete a record that is being referred to by a foreign key?

If you try to delete a record that is being referred to by a foreign key, the database management system (DBMS) will prevent the deletion to maintain referential integrity. The specific behavior depends on the delete rule specified for the foreign key constraint.

There are typically three delete rules:

  1. CASCADE: If the delete rule is set to CASCADE, deleting a record in the referenced table will automatically delete the corresponding records in other tables that reference it through foreign keys.

  2. SET NULL: If the delete rule is set to SET NULL, deleting a record in the referenced table will set the foreign key values in other tables to NULL.

  3. SET DEFAULT: If the delete rule is set to SET DEFAULT, deleting a record in the referenced table will set the foreign key values in other tables to their default values.

It is important to choose the appropriate delete rule based on the desired behavior and data integrity requirements.

3. What is a candidate key in SQL?

A candidate key is any column or minimal set of columns that could serve as the primary key of a table — meaning it uniquely identifies every row and contains no NULL values.

A table can have multiple candidate keys. Exactly one is chosen as the primary key; the remaining candidate keys become alternate keys (typically enforced with a UNIQUE NOT NULL constraint).

Example:

Consider an employees table:

employee_id email national_id name
1 [email protected] SSN-001 Alice
2 [email protected] SSN-002 Bob

Candidate keys here:

  • employee_id — unique and not null ✓
  • email — unique and not null ✓
  • national_id — unique and not null ✓

All three are candidate keys. If we choose employee_id as the primary key, email and national_id become alternate keys.

Relationship to other key types:

  • Super key: Any set of columns that uniquely identifies rows — a super key may contain extra columns. Candidate keys are minimal super keys.
  • Primary key: The chosen candidate key.
  • Alternate key: A candidate key not selected as the primary key.

Interview tip: Interviewers use this to test whether you understand that the choice of primary key is a design decision — not every unique identifier must be the primary key. Surrogate keys (auto-generated IDs) are often chosen over natural candidate keys for stability and simplicity.

↑ Back to top

Follow-up 1

Can a table have multiple candidate keys?

Yes, a table can have multiple candidate keys. Each candidate key represents a different way to uniquely identify each row in the table. However, only one candidate key can be selected as the primary key.

Follow-up 2

How does a candidate key differ from a primary key?

A candidate key is a potential primary key candidate, while a primary key is the chosen candidate key that is used to uniquely identify each row in a table. Only one candidate key can be selected as the primary key.

Follow-up 3

Can a candidate key have null values?

No, a candidate key cannot have null values. A candidate key must have unique and non-null values for each row in a table.

4. Can you explain the concept of a super key and how it differs from a primary key?

A super key is any set of one or more columns that uniquely identifies every row in a table. It may contain more columns than strictly necessary.

A candidate key (and by extension a primary key) is a minimal super key — meaning no column can be removed from it while still maintaining uniqueness.

Illustration:

Table: employees(employee_id, email, name, department)

Super key examples Minimal?
{employee_id} Yes → candidate key
{email} Yes → candidate key
{employee_id, email} No → removing either still gives uniqueness
{employee_id, name} No → name is redundant
{employee_id, email, name, department} No → many redundant columns

Hierarchy:

Super keys  ⊃  Candidate keys  ⊃  Primary key (one chosen)
  • Every candidate key is a super key, but not every super key is a candidate key
  • Every primary key is a candidate key, but not every candidate key is the primary key

Why this matters in interviews:

This question tests understanding of normalization and key minimality. BCNF (Boyce-Codd Normal Form) is defined in terms of super keys: a relation is in BCNF if, for every non-trivial functional dependency X → Y, X is a super key. Being able to identify super keys is prerequisite to evaluating BCNF compliance.

↑ Back to top

Follow-up 1

Can a super key include non-essential attributes?

Yes, a super key can include non-essential attributes. A super key is a set of attributes that can uniquely identify a tuple, but it may also contain additional attributes that are not necessary for uniqueness. These non-essential attributes are called redundant attributes. However, a primary key, which is a special type of super key, cannot include non-essential attributes.

Follow-up 2

Can a super key be a candidate key?

Yes, a super key can be a candidate key. A candidate key is a minimal super key, meaning it is a super key with no unnecessary attributes. Since a super key can include additional attributes that are not necessary for uniqueness, it is possible for a super key to be a candidate key if it does not contain any redundant attributes.

Follow-up 3

Can a table have multiple super keys?

Yes, a table can have multiple super keys. Each super key is a set of attributes that can uniquely identify a tuple in the table. Different combinations of attributes can form different super keys. However, it is important to note that a table can have only one primary key, which is a special type of super key that is chosen as the main identifier for the table.

5. What is a surrogate key in SQL and why might you use one?

A surrogate key is an artificially generated, system-assigned identifier used as a table's primary key. It has no business meaning — its sole purpose is to uniquely identify rows.

Common implementations:

  • Auto-incrementing integer: SERIAL / BIGSERIAL (PostgreSQL), AUTO_INCREMENT (MySQL), IDENTITY (SQL Server)
  • UUID/GUID: UUID (PostgreSQL, MySQL 8+), NEWID() (SQL Server)
-- PostgreSQL
CREATE TABLE customers (
    customer_id BIGSERIAL PRIMARY KEY,
    email       VARCHAR(200) NOT NULL UNIQUE,
    name        VARCHAR(100)
);

-- UUID example
CREATE TABLE events (
    event_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    event_type VARCHAR(50)
);

When to use a surrogate key:

  • The natural key is too long or complex (e.g., a composite of three columns)
  • The natural key can change over time (e.g., email address, username)
  • No natural unique identifier exists
  • You want stable, compact foreign key references

Surrogate vs natural key trade-offs:

Aspect Surrogate Key Natural Key
Stability Stable (never changes) May change (email, username)
Meaning None — opaque ID Has business meaning
Join columns Compact INT or UUID May be wide or composite
Data integrity Requires separate UNIQUE on natural columns Built-in

2026 note: UUIDs are increasingly preferred in distributed systems (no central sequence needed, globally unique across shards). However, random UUIDs (v4) cause B-tree index fragmentation; UUID v7 (time-ordered) is gaining adoption to mitigate this. PostgreSQL 17 added gen_random_uuid() defaulting to v4; some teams use uuid_generate_v7() via extensions.

↑ Back to top

Follow-up 1

What are the advantages and disadvantages of using a surrogate key?

Advantages of using a surrogate key include:

  • Uniqueness: Surrogate keys ensure that each record in a table has a unique identifier, regardless of the data contained in the record.
  • Simplified data management: Surrogate keys provide a simple and consistent way to identify records, making it easier to manage and manipulate data.
  • Performance: Surrogate keys can improve performance in certain scenarios, such as when joining tables or indexing data.

Disadvantages of using a surrogate key include:

  • Increased storage requirements: Surrogate keys add an additional column to each table, which can increase storage requirements.
  • Complexity: Surrogate keys can add complexity to the database design and may require additional logic to handle.
  • Lack of meaning: Surrogate keys do not have any inherent meaning or significance, which can make it harder to understand the data without additional context.

Follow-up 2

How does a surrogate key differ from a natural key?

A surrogate key is an artificially generated identifier that is added to a table to uniquely identify each record. It does not have any inherent meaning or significance and is typically a numeric or alphanumeric value. In contrast, a natural key is a column or set of columns in a table that has a meaningful and unique value, such as a person's social security number or a product's SKU. Natural keys are derived from the data itself and have a direct relationship to the real-world entities being represented in the database. Surrogate keys are often used when there is no suitable natural key or when the natural key is not ideal for use as a primary key.

Follow-up 3

Can a surrogate key be a primary key?

Yes, a surrogate key can be used as a primary key in a database table. In fact, one of the main reasons for using a surrogate key is to serve as the primary key for a table. Surrogate keys provide a simple and efficient way to uniquely identify records, and they can be easily managed and manipulated by the database system. However, it is important to note that a surrogate key is not the only option for a primary key, and in some cases, a natural key may be more appropriate.

Live mock interview

Mock interview: SQL Keys

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.