- Which row can be inserted without violating either constraint?
Answer: (102, 1, 'P2')
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.
- Which constraint on
Inventory ensures that each inventory row identifies an existing bin in the specified warehouse? Answer: FOREIGN KEY (warehouse_id, bin_code) REFERENCES Bins(warehouse_id, bin_code)
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.
- Which foreign-key constraint is valid under these rules?
Answer: FOREIGN KEY (contact_email) REFERENCES Customers(email)
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.
- Which statement can execute successfully?
Answer: UPDATE Employees SET department_id = 20 WHERE employee_id = 1;
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.
- Which set of constraints correctly defines
Assignments(assignment_id, user_id, project_id)? Answer: PRIMARY KEY (assignment_id), UNIQUE (user_id, project_id), plus foreign keys on user_id and project_id
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.
- Which constraint can be added to the existing
Employees data without first changing any rows? Answer: PRIMARY KEY (employee_id)
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.
- Which constraint definitions correctly complete the
Orders table?
CREATE TABLE Orders (order_id INT, customer_id INT, /* constraints */); Answer: PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
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.
- Which sequence of statements completes without a constraint violation?
Answer: INSERT INTO Customers VALUES (11); followed by INSERT INTO Orders VALUES (501, 11);
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.
- Which pair of additional rows can both be inserted?
Answer: (3, NULL) and (4, 'S8')
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.
- Which constraint design correctly supports these requirements?
Answer: PRIMARY KEY (employee_id), FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
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.
