Keys: Primary, Foreign, and Composite

Keys are how a relational database uniquely identifies rows and links related tables together. Without keys, there is no reliable way to say "this row" versus "that row," nor any way to connect an order to the customer who placed it. Keys are the backbone of both data integrity and the ability to join tables meaningfully.

Why it matters

Keys are what make a relational database relational. A primary key identifies each row, and a foreign key links a row in one table to a row in another and stops the link from pointing at nothing. Without them, a database fills up with duplicate records and orders that belong to customers who do not exist.

Primary Keys

A primary key is one column (or a combination of columns) that uniquely identifies every row in a table. A primary key column is implicitly NOT NULL and must be unique across all rows. Every table should have exactly one primary key, and most database engines automatically create an index on it to make lookups by that key fast.

Foreign Keys

A foreign key is a column in one table that references the primary key of another table, establishing a relationship between them. For example, an orders table might have a customer_id column that is a foreign key referencing customers.id. The database enforces referential integrity: it will reject an insert that references a customer_id that doesn't exist, and by default it will also reject deleting a customer who still has orders (unless cascading behavior is configured).

Composite Keys

A composite key is a primary (or unique) key made of two or more columns together, used when no single column is unique on its own. A common example is a join table like enrollments(student_id, course_id), where the combination of student and course is unique even though neither column alone is.

Surrogate Keys vs. Natural Keys

A natural key is an existing real-world attribute used as the primary key, such as an email address or a national ID number. A surrogate key is an artificial, meaningless identifier — typically an auto-incrementing integer or a UUID — introduced purely to serve as a stable primary key. Surrogate keys are generally preferred in practice because natural keys can change over time (a person can change their email) or turn out not to be as unique as assumed, which would force painful changes to every foreign key referencing them.

Example

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,           -- surrogate key
    email VARCHAR(150) UNIQUE NOT NULL -- natural candidate key, kept unique but not primary
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id), -- foreign key
    order_date DATE NOT NULL
);

CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)  -- composite key
);

All three CREATE TABLE statements succeed; order_items enforces one row per (order_id, product_id) pair.

Exercise

Create a customers table with a primary key, and an orders table whose customer_id is a foreign key to it. Insert customer 1, Asha. Insert an order for customer 1, then try to insert an order for customer 99, who does not exist; the database must reject it. Finally count the orders as total.

Expected output: total then 1

-- TODO: customers (id primary key, name)

-- TODO: orders (id primary key, customer_id referencing customers, product)

INSERT INTO customers (id, name) VALUES (1, 'Asha');
INSERT INTO orders (id, customer_id, product) VALUES (1, 1, 'pen');
INSERT INTO orders (id, customer_id, product) VALUES (2, 99, 'bag');

SELECT COUNT(*) AS total FROM orders;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product TEXT
);

INSERT INTO customers (id, name) VALUES (1, 'Asha');
INSERT INTO orders (id, customer_id, product) VALUES (1, 1, 'pen');
INSERT INTO orders (id, customer_id, product) VALUES (2, 99, 'bag');

SELECT COUNT(*) AS total FROM orders;

Quick quiz

Interview questions