SQL Quiz: Table Constraints
10 questions · exam conditions
0:00
Table ConstraintsQuestion 1 of 10

The table Bins has columns warehouse_id and bin_code, with PRIMARY KEY (warehouse_id, bin_code). A bin code can be reused in different warehouses. The Inventory table contains matching warehouse_id and bin_code columns.

Which constraint on Inventory ensures that each inventory row identifies an existing bin in the specified warehouse?

FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(warehouse_id, bin_code)
FOREIGN KEY (warehouse_id) REFERENCES Bins(warehouse_id), FOREIGN KEY (bin_code) REFERENCES Bins(bin_code)
FOREIGN KEY (warehouse_id) REFERENCES Warehouses(warehouse_id), UNIQUE (bin_code)
FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(bin_code, warehouse_id)
← Back to quizzes

SQL Quiz

SQL Quiz: Table Constraints

Practice Table Constraints in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Table Constraints, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The table Bins has columns warehouse_id and bin_code, with PRIMARY KEY (warehouse_id, bin_code). A bin code can be reused in different warehouses. The Inventory table contains matching warehouse_id and bin_code columns.

Which constraint on Inventory ensures that each inventory row identifies an existing bin in the specified warehouse?

  1. FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(warehouse_id, bin_code) (correct answer)
  2. FOREIGN KEY (warehouse_id) REFERENCES Bins(warehouse_id), FOREIGN KEY (bin_code) REFERENCES Bins(bin_code)
  3. FOREIGN KEY (warehouse_id) REFERENCES Warehouses(warehouse_id), UNIQUE (bin_code)
  4. FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(bin_code, warehouse_id)
Explanation: When you need to enforce a relationship between two tables using a composite primary key, the foreign key in the referencing table must mirror that composite key as a unit — both columns together, in the correct order, pointing to the correct columns in the parent table. In this scenario, Bins uses (warehouse_id, bin_code) as its composite primary key, meaning a bin is only uniquely identified when both values are combined. To guarantee that an Inventory row points to a real, existing bin in a specific warehouse, you need a foreign key that references both columns together. That's exactly what A does: FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(warehouse_id, bin_code) — it checks the pair as a whole, which is the only way to confirm the bin exists in that particular warehouse. B is a classic trap. Splitting the composite key into two separate foreign keys doesn't work because each key would be checked independently. bin_code alone is not a unique key in Bins (the same code can appear in multiple warehouses), so the database can't even enforce a single-column foreign key on it — and even if it could, it wouldn't verify the combination. C references a completely different table (Warehouses) and uses UNIQUE on bin_code, which does nothing to verify that the bin actually exists in Bins at all. D reverses the column order — REFERENCES Bins(bin_code, warehouse_id) — which mismatches the column mapping and would either error out or silently enforce the wrong pairing. Study tip: Whenever a parent table has a composite primary key, your foreign key must reference all those columns in the same order as they appear in the parent's key definition.

Question 2

An organization needs an Employees table in which every employee has a unique identifier. An employee may optionally report to another employee in the same table. A manager may supervise any number of employees, and a non-NULL manager ID must identify an existing employee.

Which constraint design correctly supports these requirements?

  1. PRIMARY KEY (employee_id), UNIQUE (manager_id), with no foreign-key constraint on manager_id
  2. PRIMARY KEY (employee_id), FOREIGN KEY (manager_id) REFERENCES Employees(employee_id) (correct answer)
  3. UNIQUE (employee_id), FOREIGN KEY (employee_id) REFERENCES Employees(manager_id), with no primary key
  4. PRIMARY KEY (manager_id), FOREIGN KEY (manager_id) REFERENCES Employees(employee_id), with no constraint on employee_id
Explanation: When designing a table with a self-referencing hierarchy (like employees and managers), you need to ask two questions: What uniquely identifies each row? And how do we enforce that referenced values actually exist? Every valid table needs a PRIMARY KEY to uniquely identify each employee — that's non-negotiable. The manager relationship adds a second requirement: since a manager must be an existing employee, manager_id needs a FOREIGN KEY that points back to employee_id in the same table. This is called a self-referential foreign key, and it's the classic pattern for representing hierarchies. Because manager_id is optional (an employee may have no manager), NULL values are allowed — and foreign key constraints in SQL automatically permit NULL, satisfying the "optional" requirement. That's exactly what option B provides, making it the correct answer. Option A defines a UNIQUE constraint on manager_id instead of a foreign key. This accidentally prevents two employees from sharing the same manager, which directly contradicts the requirement that a manager may supervise any number of employees. It also does nothing to verify that a manager ID refers to a real employee. Option C swaps the roles entirely — it tries to reference manager_id as the target of a foreign key, but manager_id isn't a primary or unique key, so this is structurally invalid. Dropping the primary key entirely also means rows can't be uniquely identified. Option D makes manager_id the primary key, which means every employee must have a manager and no two employees could share one — both wrong. Study tip: Whenever you see a self-referencing table, immediately look for a PRIMARY KEY on the identifier column and a FOREIGN KEY on the reference column pointing back to that same column.

Question 3

The table Employees(employee_id, employee_code, department_id) contains (1, 'A', 10), (2, 'B', 10), and (3, 'C', 99). None of these values is NULL. The table Departments has primary-key values 10 and 20. Each proposed constraint is considered separately.

Which constraint can be added to the existing Employees data without first changing any rows?

  1. UNIQUE (department_id)
  2. PRIMARY KEY (employee_id) (correct answer)
  3. FOREIGN KEY (department_id) REFERENCES Departments(department_id)
  4. FOREIGN KEY (employee_id) REFERENCES Departments(department_id)
Explanation: When adding a constraint to existing data, the database immediately validates every current row against that constraint. If even one row violates it, the constraint is rejected — no rows are modified first. PRIMARY KEY (employee_id) works because a primary key requires two things: uniqueness and no NULLs. The employee_id values are 1, 2, and 3 — all distinct and all non-NULL. Every row passes, so B is the correct answer. Now walk through why each other option fails. A — UNIQUE (department_id) — requires that no two rows share the same department_id. But employees 1 and 2 both have department_id = 10, which is a duplicate. The uniqueness check fails immediately. C — FOREIGN KEY (department_id) REFERENCES Departments(department_id) — requires that every department_id in Employees exists as a primary key in Departments. Departments only has keys 10 and 20, but employee 3 has department_id = 99, which doesn't exist there. This is a referential integrity violation, so the constraint is rejected. D — FOREIGN KEY (employee_id) REFERENCES Departments(department_id) — tries to match employee_id values (1, 2, 3) against Departments primary keys (10, 20). None of 1, 2, or 3 appears in Departments, so all three rows violate the foreign key — a complete mismatch. A useful strategy: before evaluating any constraint, mentally scan the data for the specific rule that constraint enforces. For UNIQUE, look for duplicates. For FOREIGN KEY, check whether every referencing value exists in the referenced table. For PRIMARY KEY, check for duplicates and NULLs.

Question 4

A database already contains Customers(customer_id INT PRIMARY KEY). Each order must have an identifier that is unique across all customers, and every order must belong to an existing customer.

Which constraint definitions correctly complete the Orders table?

CREATE TABLE Orders (order_id INT, customer_id INT, /* constraints */);

  1. PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) (correct answer)
  2. PRIMARY KEY (customer_id), FOREIGN KEY (order_id) REFERENCES Customers(customer_id)
  3. UNIQUE (order_id), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  4. PRIMARY KEY (order_id, customer_id), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
Explanation: When designing a table, you need to match each business rule to the right SQL constraint. Here, two rules apply: order IDs must be globally unique, and every order must reference a real customer. A PRIMARY KEY enforces both uniqueness and NOT NULL on a column, making it the right tool for a globally unique identifier. A FOREIGN KEY links a column to a primary key in another table, ensuring referential integrity — in this case, guaranteeing every order belongs to an existing customer. Option A is correct because PRIMARY KEY (order_id) ensures every order has a unique, non-null identifier across all customers, and FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ensures every order links to a valid customer. Both business rules are satisfied cleanly. Option B reverses the logic entirely — it makes customer_id the primary key and tries to reference order_id as a foreign key into Customers. This is backwards: a customer ID doesn't uniquely identify an order, and order_id has no relationship to Customers. Option C uses UNIQUE (order_id) instead of PRIMARY KEY. While UNIQUE enforces no duplicates, it permits NULL values, meaning an order could exist with no identifier at all — a weaker guarantee than what the scenario demands. Option D uses a composite primary key (order_id, customer_id), which means uniqueness is only guaranteed for the combination — the same order_id could appear for multiple customers. That violates the requirement that order IDs be unique across all customers. A quick tip: when a question says an identifier must be unique across the entire table with no exceptions, reach for PRIMARY KEY on that single column — not UNIQUE, and not a composite key.

Question 5

A database uses the rule that a UNIQUE constraint prohibits duplicate non-NULL values but permits multiple NULL values. The table Badges is defined as Badges(employee_id INT PRIMARY KEY, badge_code VARCHAR(10) UNIQUE). It currently contains (1, 'R7') and (2, NULL).

Which pair of additional rows can both be inserted?

  1. (3, NULL) and (4, 'S8') (correct answer)
  2. (3, 'R7') and (4, 'S8')
  3. (1, 'S8') and (4, NULL)
  4. (3, NULL) and (3, 'S8')
Explanation: When a column has a UNIQUE constraint, you need to check two things before inserting: does the new value duplicate an existing non-NULL value, and does the primary key already exist? The key insight here is that UNIQUE constraints treat NULL specially — since NULL represents an unknown value, no two NULLs are considered "equal," so multiple NULLs are always permitted. Option A works because (3, NULL) introduces a new primary key (3) and a NULL badge code, which doesn't conflict with the existing NULL in row 2. Then (4, 'S8') brings a new primary key (4) and a new badge code not yet in the table. Both rows insert cleanly — making A the correct answer. Option B fails immediately: (3, 'R7') tries to insert badge code 'R7', which already belongs to employee 1. That's a direct UNIQUE violation, so the entire pair is blocked. Option C fails because (1, 'S8') reuses primary key 1, which already exists. A PRIMARY KEY constraint is essentially UNIQUE + NOT NULL, so duplicate primary keys are never allowed — even if the badge code itself is new. Option D fails due to the primary key conflict between the two new rows themselves: both (3, NULL) and (3, 'S8') share primary key 3. You can't insert two rows with the same primary key even in a single batch. As a study tip, always scan for two potential violations when inserting: conflicts with existing rows (for UNIQUE and PRIMARY KEY) and conflicts within the new rows being inserted together.

Question 6

The database contains Customers(customer_id INT PRIMARY KEY) and Orders(order_id INT PRIMARY KEY, customer_id INT REFERENCES Customers(customer_id)). Initially, customer 10 and order 500 for customer 10 exist. Constraints are checked after each statement.

Which sequence of statements completes without a constraint violation?

  1. INSERT INTO Customers VALUES (11); followed by INSERT INTO Orders VALUES (501, 11); (correct answer)
  2. INSERT INTO Orders VALUES (501, 12); followed by INSERT INTO Customers VALUES (12);
  3. INSERT INTO Customers VALUES (10); followed by INSERT INTO Orders VALUES (501, 10);
  4. INSERT INTO Customers VALUES (11); followed by INSERT INTO Orders VALUES (500, 11);
Explanation: When working with foreign key constraints in SQL, the key rule to remember is: the referenced row must exist before you can reference it. Since Orders.customer_id references Customers.customer_id, any customer ID you insert into Orders must already exist in Customers at the moment that statement executes — because constraints are checked after each individual statement. Option A works correctly: you first insert customer 11 into Customers, then insert order 501 referencing customer 11. By the time the second statement runs, customer 11 already exists, so the foreign key check passes. This is the correct answer. Option B reverses the required order — it tries to insert order 501 referencing customer 12 before customer 12 exists in Customers. The foreign key constraint fires immediately after that first statement and raises a violation. Option C attempts to insert customer 10, but customer 10 already exists in the table. This violates the primary key constraint on Customers, since primary keys must be unique. The sequence fails on the very first statement, before even reaching the second. Option D inserts customer 11 successfully, then tries to insert order 500. However, order 500 already exists in Orders, violating the primary key constraint on Orders. The second statement fails even though the foreign key would have been satisfied. A helpful pattern to remember: when inserting related rows, always insert parent records first, child records second — parent tables hold the primary key, child tables hold the foreign key. Watch for questions that deliberately swap this order as a trap.

Question 7

Users may participate in many projects, and projects may have many users. The table Users has primary key user_id, and Projects has primary key project_id. Every assignment needs its own unique assignment_id, but the same user must not be assigned to the same project twice.

Which set of constraints correctly defines Assignments(assignment_id, user_id, project_id)?

  1. PRIMARY KEY (assignment_id), UNIQUE (user_id), plus foreign keys on user_id and project_id
  2. PRIMARY KEY (user_id, project_id), UNIQUE (project_id), plus foreign keys on user_id and project_id
  3. PRIMARY KEY (assignment_id), UNIQUE (user_id), UNIQUE (project_id), plus both required foreign keys
  4. PRIMARY KEY (assignment_id), UNIQUE (user_id, project_id), plus foreign keys on user_id and project_id (correct answer)
Explanation: When designing a junction table for a many-to-many relationship, you need to think carefully about two separate goals: providing a surrogate primary key for each row, and enforcing the business rule that prevents duplicate pairings. Here, the problem states two requirements: every assignment needs its own unique assignment_id, and no user can be assigned to the same project twice. The first requirement calls for PRIMARY KEY (assignment_id). The second requires that the combination of user_id and project_id be unique — enforced with UNIQUE (user_id, project_id). You also need foreign keys on both user_id and project_id to maintain referential integrity back to Users and Projects. That's exactly what D provides. A is wrong because UNIQUE (user_id) alone means each user can only appear in one assignment ever — far too restrictive. It doesn't enforce uniqueness on the pair. B makes (user_id, project_id) the primary key, which would enforce uniqueness on the pair, but then adds UNIQUE (project_id) — meaning each project could only be assigned once total. That breaks the many-to-many relationship. It also discards the required surrogate assignment_id primary key. C lists UNIQUE (user_id) and UNIQUE (project_id) as separate constraints, meaning each user and each project can only appear once in the entire table. That's far too restrictive and misunderstands how composite uniqueness works — the constraint must be on the pair, not on each column individually. A helpful rule of thumb: when you see "the same X must not be combined with the same Y twice," that signals a composite UNIQUE constraint on both columns together, not separate constraints on each.

Question 8

Assume a foreign key must reference the exact column list of a declared PRIMARY KEY or UNIQUE constraint, with compatible data types. Customers is defined with customer_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, and ordinary column region_code CHAR(2). Logins contains contact_email VARCHAR(100) and customer_region CHAR(2).

Which foreign-key constraint is valid under these rules?

  1. FOREIGN KEY (contact_email, customer_region) REFERENCES Customers(email, region_code)
  2. FOREIGN KEY (customer_region) REFERENCES Customers(region_code)
  3. FOREIGN KEY (contact_email) REFERENCES Customers(customer_id)
  4. FOREIGN KEY (contact_email) REFERENCES Customers(email) (correct answer)
Explanation: When working with foreign keys, you need to verify two things simultaneously: the referenced column(s) must be covered by a PRIMARY KEY or UNIQUE constraint, and the data types must be compatible between the referencing and referenced columns. In the Customers table, there are exactly two valid reference targets: customer_id (covered by PRIMARY KEY) and email (covered by UNIQUE). The column region_code has no such constraint, making it off-limits as a reference target regardless of data type. Option D — FOREIGN KEY (contact_email) REFERENCES Customers(email) — is the valid choice. The Logins column contact_email is VARCHAR(100), and Customers.email is also VARCHAR(100), so types match. More importantly, email is declared UNIQUE, satisfying the constraint requirement. This one checks every box. Option A fails because it references region_code as part of a composite key. Even though email is valid on its own, region_code has no PRIMARY KEY or UNIQUE constraint, so the entire composite reference is invalid. Option B fails for the same core reason — region_code alone is just an ordinary column with no uniqueness guarantee, so the database cannot use it as a reference target. Option C fails on two fronts: contact_email is VARCHAR(100) but customer_id is INT, creating a type mismatch, and even if types matched, you'd be referencing the right constraint but with an incompatible column. A good strategy: before evaluating any foreign key, immediately scan the referenced table and mentally highlight only its PRIMARY KEY and UNIQUE columns — those are your only legal targets.

Question 9

The table OrderLines has PRIMARY KEY (order_id, line_no) and UNIQUE (order_id, product_id). It currently contains (100, 1, 'P1'), (100, 2, 'P2'), and (101, 1, 'P1'), where the values are listed as (order_id, line_no, product_id).

Which row can be inserted without violating either constraint?

  1. (100, 2, 'P3')
  2. (100, 3, 'P1')
  3. (102, 1, 'P2') (correct answer)
  4. (101, 1, 'P4')
Explanation: When a table has multiple constraints, every inserted row must satisfy all of them simultaneously. Here, you need to check two things for each candidate row: (1) does the (order_id, line_no) pair already exist (PRIMARY KEY violation)? and (2) does the (order_id, product_id) pair already exist (UNIQUE violation)? Option C, (102, 1, 'P2'), is the correct insertion. Order 102 doesn't appear anywhere in the table, so (102, 1) is a fresh primary key. The pair (102, 'P2') also doesn't exist under any order, so the unique constraint is satisfied as well — both checks pass cleanly. Option A, (100, 2, 'P3'), fails immediately on the primary key: (100, 2) already belongs to the row (100, 2, 'P2'). Option B, (100, 3, 'P1'), clears the primary key check since (100, 3) is new, but then hits the unique constraint — (100, 'P1') already exists in the row (100, 1, 'P1'). This is the classic trap: students often only check the primary key and forget the unique constraint. Option D, (101, 1, 'P4'), fails on the primary key because (101, 1) is already taken by (101, 1, 'P1'). A reliable strategy is to build a quick mental checklist: for each candidate row, explicitly verify every named constraint before marking it safe. Questions like this are specifically designed to let one constraint pass while the other fails — always check both.

Question 10

The table Departments contains department IDs 10 and 20. The table Employees contains employee 1 assigned to department 10. Employees.department_id is a foreign key referencing Departments.department_id. No cascading action is defined, and the database rejects changes that would violate the foreign key.

Which statement can execute successfully?

  1. UPDATE Departments SET department_id = 30 WHERE department_id = 10;
  2. DELETE FROM Departments WHERE department_id = 10;
  3. UPDATE Employees SET department_id = 20 WHERE employee_id = 1; (correct answer)
  4. UPDATE Employees SET department_id = 30 WHERE employee_id = 1;
Explanation: When working with foreign keys, you need to think directionally: a foreign key in a child table points to a parent table, and the constraint protects referential integrity. The key question is always which direction a change flows and whether it breaks any existing references. The foreign key here means Employees.department_id must always match a value that exists in Departments.department_id. With that in mind, C is the correct answer — updating employee 1's department from 10 to 20 is perfectly valid because department 20 already exists in the Departments table. You're simply reassigning an employee to another legitimate department. No reference is broken. Option A fails because department 10 is currently referenced by employee 1. Changing the parent table's primary key to 30 would leave employee 1 pointing to a department that no longer exists — a direct foreign key violation. Without a cascading update defined, the database rejects this. Option B has the same problem: you can't delete department 10 from the parent table while a child row (employee 1) still references it. Deletion would orphan that employee record, so the database blocks it. Option D fails from the child side — you'd be setting employee 1's department to 30, but 30 doesn't exist anywhere in Departments. The foreign key requires the value to be present in the parent table first. A useful mental model: parent changes break children below them; child changes must find a valid parent above them. When no cascading is set, any change that would violate either rule is rejected outright.