SQLDatabase Fundamentalsbeginner40 min read

Understanding Primary Keys and Foreign Keys

Learn how primary keys uniquely identify rows, how foreign keys connect related tables, and how referential actions protect relational data.

On This Page (Table of Contents)

In this lesson, you will learn

✓Define primary keys and explain their uniqueness and non-null requirements
✓Distinguish surrogate keys from natural keys
✓Define single-column and composite primary keys
✓Establish relationships using foreign key constraints
✓Explain ON DELETE and ON UPDATE referential actions
✓Choose suitable referential actions for common database scenarios
✓Identify when indexes on foreign key columns are useful
✓Diagnose common primary key and foreign key constraint errors

Why Do Databases Need Keys?

Imagine a customers table containing thousands of people. Several customers may share the same name, and two people might even share the same name and city. A name alone is therefore not a reliable way to identify a specific customer. A key gives a row a dependable identity or connects it to a row in another table. Relational databases use primary keys and foreign keys to support these two jobs.

Why Names Are Not Reliable Identifiers

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE customers_example (
  customer_id INTEGER PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);
 
INSERT INTO customers_example (customer_id, full_name)
VALUES
  (1, 'Aman Sharma'),
  (2, 'Aman Sharma');
 
SELECT customer_id, full_name
FROM customers_example
ORDER BY customer_id;

Output

customer_id | full_name -------------+------------- 1 | Aman Sharma 2 | Aman Sharma (2 rows)

The two customers have the same name but different IDs. The primary key identifies each row without relying on the name being unique.

Understanding Primary Keys

A primary key (PK) is a constraint that identifies each row in a table. Its value must be unique and cannot be NULL. A table can have only one primary key constraint, although that constraint can include one column or several columns. In PostgreSQL, defining a primary key also creates a unique B-tree index to enforce uniqueness and support lookups.

Defining a Primary Key

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE students (
  student_id BIGINT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);
 
INSERT INTO students (student_id, full_name)
VALUES (101, 'Riya');
 
-- Fails: duplicate primary key
INSERT INTO students (student_id, full_name)
VALUES (101, 'Arjun');
 
-- Fails: primary key cannot be NULL
INSERT INTO students (student_id, full_name)
VALUES (NULL, 'Meera');

Output

The first INSERT succeeds. The next two INSERT statements fail because they violate the primary key constraint.

The primary key prevents two rows from sharing the same student_id and prevents a row from having a missing student_id.

Remember These Three Rules

A primary key must be unique, must not contain NULL values, and there can be only one primary key constraint per table. That key can still consist of multiple columns.

Surrogate Keys and Natural Keys

A surrogate key is an identifier created for database use, such as an identity integer or UUID. A natural key is an existing business attribute that can identify a record, such as a carefully defined product code. Surrogate keys are often convenient because they are independent of changing business details. Natural keys can also be appropriate when the business guarantees that an attribute is unique and stable.

Do Not Assume Every Natural Key Is Stable

An email address may change or be shared in some systems. Before using a real-world attribute as a key, verify its uniqueness, stability, and business rules.

Comparing Key Types

Aspect	Surrogate Key	Natural Key
Source	Created for identification	Comes from a real-world or business attribute
Example	customer_id	A guaranteed-unique customer number
Change risk	Usually low	Depends on whether the business attribute can change
Common use	Internal row identification	Business identifiers and external references

Creating an Auto-Generated Primary Key

In PostgreSQL, GENERATED ALWAYS AS IDENTITY automatically generates values for a column. Adding PRIMARY KEY separately makes that column the table's primary key. Identity generation and primary key enforcement solve different problems: identity generates values, while the primary key enforces uniqueness and non-nullness.

An Identity Primary Key

sql

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL
);
 
INSERT INTO products (product_name)
VALUES ('Keyboard'), ('Mouse');
 
SELECT product_id, product_name
FROM products
ORDER BY product_id;

Output

Two product rows with automatically generated product_id values. The exact generated numbers depend on the sequence state.

You omit product_id from the INSERT because PostgreSQL generates it. The primary key ensures that generated IDs cannot identify two different rows.

Understanding Composite Primary Keys

A composite primary key contains two or more columns. The combination must be unique, even though individual columns may repeat. Composite keys are useful for records whose identity naturally depends on a combination of values, such as the membership of a student in a course.

A Composite Primary Key

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE course_enrollments (
  student_id BIGINT NOT NULL,
  course_id BIGINT NOT NULL,
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO course_enrollments (student_id, course_id)
VALUES (10, 501), (10, 502), (11, 501);
 
-- Fails: this student-course combination already exists
INSERT INTO course_enrollments (student_id, course_id)
VALUES (10, 501);

Output

The first three enrollment rows succeed. The final INSERT fails because the combination (10, 501) already exists.

A student can enroll in multiple courses, and a course can have multiple students. The pair student_id and course_id uniquely identifies each enrollment.

What Is a Foreign Key?

A foreign key (FK) is a constraint on one or more columns in a child table. It requires each non-null foreign key value to match a referenced key in the parent table. The referenced columns must be a primary key or an eligible unique key. This prevents a child row from referring to a parent that does not exist.

A Foreign Key Relationship

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),
 
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);
 
INSERT INTO customers (email, full_name)
VALUES ('riya@example.com', 'Riya');
 
INSERT INTO orders (customer_id, total_amount)
VALUES (1, 799.00);

Output

Both tables are created. The order INSERT succeeds only if customer_id 1 exists in customers and the generated customer ID is 1.

The foreign key enforces the relationship. For applications and tutorials, use the actual generated customer ID rather than assuming it will always be 1.

Parent and Child

The parent table contains the referenced identifier. The child table contains the foreign key. For example, customers is the parent of orders when orders.customer_id references customers.customer_id.

Seeing a Foreign Key Reject Invalid Data

A foreign key protects the relationship even when data is inserted directly through a SQL client rather than through an application. If the referenced customer does not exist, PostgreSQL rejects the order.

An Invalid Reference

sql

1
2
3
-- Assuming customer_id 999999 does not exist:
INSERT INTO orders (customer_id, total_amount)
VALUES (999999, 450.00);

Output

ERROR: insert or update on table violates foreign key constraint

The referenced customer row is missing, so the database rejects the order. The exact error wording may vary by PostgreSQL version and constraint name.

Foreign Keys and NULL Values

A foreign key column can contain NULL unless it is also declared NOT NULL. NULL means there is no referenced value for that row; it does not mean that a parent with an ID of NULL must exist. If the relationship is mandatory, use NOT NULL with the foreign key. If the relationship is optional, NULL may be appropriate.

Mandatory and Optional Relationships

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  department_id INTEGER NOT NULL
    REFERENCES departments(department_id)
);
 
CREATE TABLE support_tickets (
  ticket_id INTEGER PRIMARY KEY,
  assigned_department_id INTEGER
    REFERENCES departments(department_id)
);

Output

employees requires a department for each employee. support_tickets allows assigned_department_id to be NULL.

NOT NULL controls whether the relationship is required. The foreign key controls whether a supplied non-null value refers to a valid department.

Choosing ON DELETE Actions

A foreign key's ON DELETE action defines what happens to referencing child rows when a parent row is deleted. Choose the action according to the meaning of the data. PostgreSQL uses NO ACTION when no delete action is specified. RESTRICT and NO ACTION both prevent a deletion that would violate the relationship in ordinary immediate-constraint cases, but they differ in when the check is performed when deferred constraints are involved.

Common ON DELETE Actions

Action	Behavior	Suitable example
NO ACTION	Rejects a deletion if the resulting data would violate the foreign key; this is the default.	Preserving referenced records
RESTRICT	Rejects a parent deletion while referencing child rows exist.	Prevent deleting a customer with orders
CASCADE	Deletes referencing child rows automatically.	Removing an order and its dependent draft line items
SET NULL	Sets the referencing foreign key columns to NULL.	Removing an optional assignee while keeping a ticket
SET DEFAULT	Sets the referencing foreign key columns to their declared defaults; those values must still satisfy the foreign key.	Assigning a fallback record when it is guaranteed to exist

Using ON DELETE RESTRICT

RESTRICT is useful when a parent must not be deleted while dependent records exist. It forces the application or database operator to deal with the dependent data explicitly before removing the parent.

Prevent Deleting a Customer with Orders

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE customers_restrict (
  customer_id INTEGER PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE orders_restrict (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers_restrict(customer_id)
    ON DELETE RESTRICT
);
 
INSERT INTO customers_restrict VALUES (1, 'Riya');
INSERT INTO orders_restrict VALUES (101, 1);
 
-- Fails while order 101 references customer 1:
DELETE FROM customers_restrict
WHERE customer_id = 1;

Output

The DELETE fails because orders_restrict still contains a row referencing customer 1.

The customer must be retained, or the dependent order must be handled according to the application's rules before deletion.

Using ON DELETE CASCADE

CASCADE automatically deletes child rows when their referenced parent row is deleted. It is appropriate when child records have no independent meaning without their parent. It can also delete many rows indirectly, so review the relationship before using it.

Deleting an Order and Its Line Items

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE orders_cascade (
  order_id INTEGER PRIMARY KEY
);
 
CREATE TABLE order_items_cascade (
  order_id INTEGER NOT NULL
    REFERENCES orders_cascade(order_id)
    ON DELETE CASCADE,
  item_number INTEGER NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (order_id, item_number)
);
 
INSERT INTO orders_cascade VALUES (500);
INSERT INTO order_items_cascade
  (order_id, item_number, product_name)
VALUES
  (500, 1, 'Keyboard'),
  (500, 2, 'Mouse');
 
DELETE FROM orders_cascade
WHERE order_id = 500;
 
SELECT *
FROM order_items_cascade;

Output

The SELECT returns zero rows because the two child line items were deleted by the cascade.

The line items belong to the order. Deleting the order automatically removes its dependent items. Do not apply this pattern to records that must be retained for auditing or legal reasons.

Using ON DELETE SET NULL

SET NULL preserves the child row but clears its foreign key value when the parent is deleted. The affected foreign key column must allow NULL. This is useful when the relationship is optional and the child record remains meaningful without the parent.

Keep a Ticket When Its Assignee Is Removed

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE agents (
  agent_id INTEGER PRIMARY KEY,
  agent_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  assigned_agent_id INTEGER NULL,
  FOREIGN KEY (assigned_agent_id)
    REFERENCES agents(agent_id)
    ON DELETE SET NULL
);
 
INSERT INTO agents VALUES (7, 'Aman');
INSERT INTO tickets VALUES (900, 7);
 
DELETE FROM agents
WHERE agent_id = 7;
 
SELECT ticket_id, assigned_agent_id
FROM tickets;

Output

ticket_id | assigned_agent_id -----------+------------------- 900 | NULL (1 row)

The ticket remains, but its assigned_agent_id becomes NULL. If the column were NOT NULL, this action would fail.

Understanding ON UPDATE Actions

ON UPDATE defines what happens to referencing rows when the referenced key value changes. Primary key values are often designed to be stable, so updates may be uncommon. With ON UPDATE CASCADE, the new referenced value propagates to the child foreign key columns. With RESTRICT or NO ACTION, an update that would break the relationship is rejected.

Propagating an Updated Key

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE departments_update (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees_update (
  employee_id INTEGER PRIMARY KEY,
  department_id INTEGER NOT NULL,
  FOREIGN KEY (department_id)
    REFERENCES departments_update(department_id)
    ON UPDATE CASCADE
);
 
INSERT INTO departments_update
VALUES (10, 'Engineering');
 
INSERT INTO employees_update
VALUES (1, 10);
 
UPDATE departments_update
SET department_id = 20
WHERE department_id = 10;
 
SELECT employee_id, department_id
FROM employees_update;

Output

employee_id | department_id -------------+--------------- 1 | 20 (1 row)

The referenced department ID changes from 10 to 20, and the employee's foreign key is updated automatically.

Composite Foreign Keys

A foreign key can contain multiple columns. The referencing columns must match the referenced key columns in number and compatible types. Composite foreign keys are useful when a relationship depends on more than one identifier, such as an enrollment that must refer to a valid student-course pair.

Referencing a Composite Primary Key

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE course_catalog (
  course_id INTEGER PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE valid_enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL
    REFERENCES course_catalog(course_id),
  PRIMARY KEY (student_id, course_id)
);
 
CREATE TABLE enrollment_results (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  grade VARCHAR(2),
  FOREIGN KEY (student_id, course_id)
    REFERENCES valid_enrollments(student_id, course_id)
);

Output

The three tables are created. enrollment_results must reference an existing student-course combination in valid_enrollments.

A multi-column foreign key checks the combination as a whole. It cannot be replaced by checking each column independently when the relationship depends on their pairing.

Adding a Foreign Key to an Existing Table

You can add a foreign key after creating a table by using ALTER TABLE. PostgreSQL validates existing rows when the constraint is added unless you use a workflow such as NOT VALID followed by VALIDATE CONSTRAINT. Existing invalid references must be corrected before a normally validated constraint can be added.

Add a Relationship After Table Creation

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE departments_alter (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees_alter (
  employee_id INTEGER PRIMARY KEY,
  department_id INTEGER NOT NULL
);
 
ALTER TABLE employees_alter
ADD CONSTRAINT fk_employees_department
FOREIGN KEY (department_id)
REFERENCES departments_alter(department_id);

Output

The foreign key is added if every existing non-null department_id in employees_alter matches a department in departments_alter.

Adding a constraint does not automatically repair bad data. Check existing rows before adding a foreign key to a populated table.

Inspecting Key Constraints

In PostgreSQL, psql provides the \d command to inspect a table's columns, indexes, and constraints. You can also query information_schema to inspect primary and foreign key relationships using portable metadata views.

Inspect Foreign Key Constraints

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- In psql:
\d orders
 
-- Query foreign key metadata:
SELECT
  tc.constraint_name,
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
 AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_name, kcu.ordinal_position;

Output

The query lists foreign key constraint names, child columns, and their referenced table and column for visible constraints in the public schema.

The metadata query helps you inspect relationships without changing the database. For schemas other than public, adjust the schema filter.

Why Foreign Key Indexes Matter

A primary key or unique constraint creates an index on the referenced key in PostgreSQL. However, PostgreSQL does not automatically create an index on the child table's referencing columns. Such an index can improve joins and help the database find dependent rows when a parent is deleted or its referenced key is updated. Whether to add one depends on table size, query patterns, and write overhead.

Index a Frequently Used Foreign Key

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
 
-- A common relationship query:
SELECT
  c.customer_id,
  c.full_name,
  o.order_id,
  o.total_amount
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id = 1;

Output

The index is created if it does not already exist. The SELECT returns orders belonging to the customer with ID 1.

An index may help PostgreSQL locate matching orders efficiently. It does not guarantee that the query planner will always use the index.

Common Constraint Errors

Key constraints protect data by rejecting invalid operations. Understanding the type of violation helps you fix the underlying problem instead of removing a useful constraint.

Typical Errors and Fixes

Problem	Likely cause	What to check
Duplicate key value	A primary key or unique key value already exists.	Check whether the row already exists and whether the identifier is correct.
Foreign key violation on INSERT	The supplied parent ID does not exist.	Create or select the valid parent before inserting the child.
Foreign key violation on DELETE	A child row still references the parent.	Review dependent rows and the intended deletion policy.
Cannot set value to NULL	A NOT NULL constraint conflicts with SET NULL or the supplied value.	Confirm whether the relationship is optional and choose a compatible schema.
Cannot add foreign key	Existing rows may contain invalid references or the referenced columns are not eligible as a key.	Validate existing data and confirm the referenced key definition.

MCQ 1: Primary Key Rules

Which statement about a primary key is correct?

MCQ 2: Composite Keys

Check whether you understand how a composite primary key works.

MCQ 3: Foreign Key Purpose

Choose the statement that best describes a foreign key.

MCQ 4: ON DELETE CASCADE

Consider the effect of cascading deletion.

MCQ 5: ON DELETE SET NULL

Consider a child table that keeps records after a referenced parent is removed.

MCQ 6: RESTRICT and NO ACTION

Understand the default behavior when a referenced parent is deleted.

MCQ 7: Foreign Key Indexes

Check your understanding of indexes on referencing columns.

MCQ 8: Mandatory Relationships

A foreign key and a NOT NULL constraint serve related but different purposes.

Guided Practice: Build a Library Relationship

Create a small library database. Each book copy belongs to a valid book record. First write the SQL yourself, then compare it with the solution. The exercise uses a surrogate primary key for each book and a foreign key from book_copies to books.

Practice Requirements

sql

1
2
3
4
5
6
-- Requirements:
-- 1. Create books with book_id as the primary key.
-- 2. Give each book a required title.
-- 3. Create book_copies with copy_id as the primary key.
-- 4. Require each copy to reference a valid book.
-- 5. Prevent deleting a book while copies still exist.

Output

Two related tables with a primary key on each table and a RESTRICT foreign key relationship from book_copies to books.

Think about which table owns the original book record and which table contains dependent copies. The copies table should reference the books table.

Guided Practice Solution

Compare your solution with this version. The foreign key prevents a copy from referencing a missing book, and RESTRICT prevents deleting a book while copies reference it.

Library Tables Solution

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE books (
  book_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);
 
CREATE TABLE book_copies (
  copy_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id BIGINT NOT NULL,
  condition_note VARCHAR(100),
  CONSTRAINT fk_book_copies_book
    FOREIGN KEY (book_id)
    REFERENCES books(book_id)
    ON DELETE RESTRICT
);
 
CREATE INDEX idx_book_copies_book_id
ON book_copies(book_id);
 
INSERT INTO books (title)
VALUES ('Learning SQL');
 
INSERT INTO book_copies (book_id, condition_note)
SELECT book_id, 'Good'
FROM books
WHERE title = 'Learning SQL';

Output

The tables and index are created. The INSERT statements add a book and a copy that references the book's actual generated ID.

The INSERT ... SELECT pattern avoids assuming which identity value PostgreSQL will generate. In a real application, use a stable identifier to select the intended book if titles are not unique.

Interview Questions and Answers

Use these questions to test whether you can explain key constraints and relationship behavior in an entry-level SQL interview.

Lesson Summary

Primary keys identify rows; foreign keys connect rows across tables. A primary key must be unique and non-null, while a foreign key ensures that supplied non-null references exist. Composite keys use a combination of columns. Referential actions determine what happens when referenced keys are deleted or updated. Choose CASCADE, RESTRICT, SET NULL, and other actions according to the meaning and lifecycle of the data. In PostgreSQL, consider indexing child foreign key columns when the workload benefits from it.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming a foreign key automatically makes the child column unique

Why it happens: A foreign key checks whether a referenced value exists, but it does not prohibit multiple child rows from referencing the same parent.

Incorrect Approach

sql

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id)
);

Correct Solution

sql

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

Key takeaway: Multiple orders can reference the same customer. Add UNIQUE to customer_id only if the business rule truly requires each customer to appear in at most one order.

#2 Using ON DELETE CASCADE without considering dependent data

Why it happens: CASCADE is convenient, but deleting one parent can automatically remove many child rows.

Incorrect Approach

sql

FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE

Correct Solution

sql

FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT

Key takeaway: Use RESTRICT when dependent records must be reviewed or retained. The correct action depends on the application; CASCADE is not universally wrong.

#3 Using SET NULL on a NOT NULL column

Why it happens: The referential action attempts to clear the foreign key, but the column definition forbids NULL.

Incorrect Approach

sql

assigned_agent_id INTEGER NOT NULL REFERENCES agents(agent_id) ON DELETE SET NULL

Correct Solution

sql

assigned_agent_id INTEGER REFERENCES agents(agent_id) ON DELETE SET NULL

Key takeaway: If the relationship is optional, allow NULL. If it is mandatory, choose another deletion policy.

#4 Assuming identity generation automatically creates a primary key

Why it happens: Automatically generated values may look unique, but value generation and uniqueness constraints are separate concerns.

Incorrect Approach

sql

product_id BIGINT GENERATED ALWAYS AS IDENTITY

Correct Solution

sql

product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Key takeaway: Declare PRIMARY KEY when the column is meant to identify each row. Identity generation alone is not a substitute for a primary key constraint.

#5 Forgetting that foreign key columns can contain duplicates

Why it happens: Learners sometimes assume that every key column must be unique.

Incorrect Approach

sql

Adding UNIQUE to every foreign key even when many child rows can share a parent.

Correct Solution

sql

Use a normal foreign key for a one-to-many relationship. Add UNIQUE only when the relationship requires at most one child row per parent.

Key takeaway: For example, many orders can belong to one customer. A unique constraint on orders.customer_id would incorrectly restrict that relationship.

#6 Assuming PostgreSQL automatically indexes the child foreign key

Why it happens: The referenced primary key is indexed, which can lead learners to assume the child side is indexed too.

Incorrect Approach

sql

CREATE TABLE order_items (
  item_id BIGINT PRIMARY KEY,
  order_id BIGINT REFERENCES orders(order_id)
);

Correct Solution

sql

CREATE TABLE order_items (
  item_id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(order_id)
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Key takeaway: The index is optional and workload-dependent, but it is often useful for joins and locating child rows. Do not create indexes blindly without considering query patterns and write costs.

#7 Assuming every parent row must have a child row

Why it happens: A foreign key is mistaken for a rule that enforces relationships in both directions.

Incorrect Approach

sql

Expecting a foreign key from orders to customers to require every customer to have an order.

Correct Solution

sql

Use the foreign key to ensure every order references a valid customer. Handle any requirement that every customer must have an order with additional business logic or a suitable design.

Key takeaway: A foreign key protects references from child to parent; it does not require each parent to be referenced.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Understanding Primary Keys and Foreign Keys Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

Which statement about a primary key is correct?

Question 2 of 8

A table has PRIMARY KEY (student_id, course_id). Which pair is allowed?

Question 3 of 8

What does a foreign key constraint enforce?

Question 4 of 8

What happens when a parent row is deleted under ON DELETE CASCADE?

Question 5 of 8

What is required for ON DELETE SET NULL to work for a child foreign key column?

Question 6 of 8

If a foreign key has no ON DELETE clause, what is PostgreSQL's default action?

Question 7 of 8

In PostgreSQL, does defining a foreign key automatically create an index on the child foreign key column?

Question 8 of 8

How do you require every order to reference an existing customer?

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What is a primary key?

Answer:

A primary key is a constraint that uniquely identifies each row. It enforces uniqueness and disallows NULL values.

Q2:Can a table have two primary keys?

Answer:

No. A table can have one primary key constraint. That constraint can contain multiple columns, making it a composite primary key.

Q3:What is a foreign key?

Answer:

A foreign key ensures that each non-null referencing value matches a key in another table or an eligible key in the same table.

Q4:What is the difference between a primary key and a foreign key?

Answer:

A primary key identifies rows in its own table. A foreign key links a row to a referenced row and enforces referential integrity.

Q5:What is a composite primary key?

Answer:

It is one primary key constraint made from two or more columns. The combination must be unique, and every component must be non-null.

Q6:What is a surrogate key?

Answer:

A surrogate key is an identifier created for database use, such as an identity integer or UUID, rather than being taken directly from a business attribute.

Q7:What is a natural key?

Answer:

A natural key is a real-world or business attribute that identifies a record under defined uniqueness rules.

Q8:What does ON DELETE CASCADE do?

Answer:

It automatically deletes child rows that reference a parent row when that parent is deleted.

Q9:What is the difference between RESTRICT and CASCADE?

Answer:

RESTRICT blocks a parent deletion while referencing child rows exist. CASCADE deletes those referencing child rows.

Q10:What does ON DELETE SET NULL do?

Answer:

It preserves child rows but sets their foreign key values to NULL. The affected columns must allow NULL.

Q11:What is the default ON DELETE action in PostgreSQL?

Answer:

NO ACTION. A deletion that would violate the foreign key is rejected.

Q12:Does a foreign key automatically create an index on the child column in PostgreSQL?

Answer:

No. Add an index when it benefits joins, parent deletion or updates, or other common queries.

Q13:Can a foreign key contain NULL?

Answer:

Yes, unless the referencing column has a NOT NULL constraint. A non-null value must match a referenced key.

Q14:Can a foreign key reference a column other than the primary key?

Answer:

Yes, if the referenced column or column combination has an eligible unique key, such as a UNIQUE constraint.

Q15:Why might adding a foreign key to an existing table fail?

Answer:

Existing child rows may reference missing parents, or the referenced columns may not have an eligible key constraint.

Q16:What does ON UPDATE CASCADE do?

Answer:

It propagates changes to the referenced key value to referencing foreign key values.

Q17:Does a foreign key guarantee that every parent has a child?

Answer:

No. It ensures that child references are valid. It does not require every parent to be referenced by a child.

Key takeaways

A primary key uniquely identifies each row and does not allow NULL values.
A table can have only one primary key constraint, but that key may contain multiple columns.
A foreign key ensures that non-null referenced values match an eligible key in the referenced table.
A foreign key may reference a primary key or another eligible unique key.
ON DELETE CASCADE deletes dependent child rows; use it only when that behavior is intended.
ON DELETE RESTRICT prevents deletion of a referenced parent row while dependent rows exist.
ON DELETE SET NULL requires the child foreign key column to allow NULL values.
Foreign key constraints do not automatically create an index on the referencing columns in PostgreSQL.
Choose keys and referential actions according to the relationship and data lifecycle.

Was this lesson helpful?

Found an error? Report an issue