Questions: Primary Keys and Foreign Keys

Question 1

A developer deletes a row from the Customers table for a customer who has 5 active orders in the Orders table. The Orders.customer_id column is a foreign key referencing Customers.id, with no cascade behavior configured. What happens?

• A. The customer is deleted and the 5 orders are automatically deleted as well
• B. The customer is deleted and the orders' customer_id values are set to NULL
• C. The database rejects the deletion with a constraint violation
• D. The customer row is marked as deleted but retained until the orders are removed

Answer: (C)

Without ON DELETE CASCADE or ON DELETE SET NULL, referential integrity prevents deleting a parent row that has referencing children. The database enforces this actively: the deletion is rejected with a foreign key constraint violation. This is the whole point of foreign keys — preventing orphaned records where orders reference a customer who no longer exists. The developer must either delete or reassign the orders first, or configure cascade behavior explicitly if automatic deletion is desired.

Question 2

A developer proposes using users' email addresses as the primary key in the Users table since emails are unique per user. What is the primary risk of this design?

• A. Email addresses are too long to be indexed efficiently by most databases
• B. Primary keys must be integers; string values are not permitted
• C. Email addresses can change, which would require updating every foreign key reference across all dependent tables
• D. Email uniqueness cannot be guaranteed at the database level

Answer: (C)

This is the core argument for surrogate keys over natural keys: natural keys can change in the real world. When a user changes their email, every Orders.user_email, Messages.sender_email, and other foreign key column referencing that value must also be updated — or the references break. A surrogate key (auto-incrementing integer or UUID) never changes, so updating a user's email is a single-row change with no cascade of foreign key updates. The email can still be enforced as UNIQUE for lookup purposes without serving as the primary key.

Question 3

A primary key should typically consist of a single column.

• T. True
• F. False

Answer: False

A primary key can span multiple columns — this is called a composite key. For example, an Enrollments table relating students to courses might use a composite primary key of (student_id, course_id), where neither column is unique alone but the combination is. The requirement is that the primary key uniquely identifies each row and contains no NULLs; the number of columns is unrestricted.

Question 4

A foreign key column in one table should have the same name as the primary key column it references in another table.

• T. True
• F. False

Answer: False

Foreign keys only need to match the data type and value domain of the referenced primary key — the column names can be entirely different. For example, Orders.buyer_id might reference Users.id. The foreign key constraint specifies the relationship explicitly: FOREIGN KEY (buyer_id) REFERENCES Users(id). Requiring matching names would make schemas less readable and more rigid without any technical benefit.

Question 5

Why is enforcing referential integrity through database constraints preferable to relying on application code to check validity?

Model answer: Application code can be bypassed — data can be inserted or deleted through multiple interfaces (direct database access, scripts, different application versions, batch imports) that may not all run the same validation logic. Database constraints are enforced unconditionally, regardless of how data arrives. They also eliminate race conditions where two concurrent transactions might each check validity, both pass, and then both insert, leaving an inconsistent state. Constraints push correctness down to the data layer where it is universal and atomic.

The principle is that data integrity should be guaranteed by the layer closest to the data, not by layers above it. Application code is fragile: it can have bugs, be bypassed by maintenance scripts, or be inconsistently updated across code versions. A NOT NULL constraint, UNIQUE constraint, or foreign key constraint fires for every transaction, from every source, in every scenario — including edge cases that application developers forgot to handle. This is why database constraints are not optional decorations but structural backbone.