Databases & SQL Practice Question 5: Primary Foreign Keys
Databases & SQL · Test 1 · question 5 of 10
An application has a customers table with primary key id, and an orders table with a customer_id column. A customer can place many orders. A bug recently inserted orders whose customer_id matches no customer at all. Which constraint makes the database itself reject such rows?
Show answer and explanation
Answer:
A foreign key constraint requires that every value in the referencing column matches a value in the referenced table's key, or is NULL. This is called referential integrity. With the constraint in place, an insert or update that sets customer_id to a non-existent customer fails, and so, by default, does deleting a customer who still has orders.
Option A confuses the two kinds of key. A primary key identifies rows in *its own* table: it enforces unique, non-null values in orders, and it never looks at customers. It would also allow each customer only one order, which contradicts the stem.
Option C constrains the wrong table. customers.id is already unique because it is the primary key, and a constraint on customers does nothing to stop bad values being written into orders.
Option D stops customer_id from being empty, but an existing value such as 99999 still passes even if no customer 99999 exists. You would use NOT NULL *together with* the foreign key if every order must have a customer, but it cannot replace the foreign key.
Reference: PostgreSQL: Constraints (primary keys, foreign keys)