Explain the differences between a primary key, a foreign key, and a unique key.

Instruction: Define each type of key and discuss how they differ and relate to each other in a database.

Context: This question tests the candidate's understanding of key database concepts and their ability to distinguish between different types of keys and their uses in establishing relationships and ensuring data uniqueness.

Updated

Example Answer

A primary key identifies a row and enforces uniqueness and non-null values. A table has one primary-key constraint, which can include several columns. A unique constraint prevents duplicate key values, but its treatment of NULL depends on the database and options. In PostgreSQL, NULLs are distinct by default, so a nullable unique column can contain multiple NULLs; NULLS NOT DISTINCT changes that behavior.

A foreign key enforces a relationship to an eligible key in another table, or sometimes the same table. Many child rows can reference the same parent, so the foreign-key column is not automatically unique. For example, each order has its own primary key, while several orders can share a customer_id referencing the customer table. Whether a child reference can be NULL depends on its nullability and constraint definition. I would also choose explicit update and delete behavior for the relationship.

Worked example

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

Two orders can reference one customer; two non-NULL equal email values violate the unique constraint.

Why this works

It distinguishes row identity, duplicate prevention, and referential integrity, while naming the engine for NULL behavior.

Related Questions