SQL Quiz: Primary And Foreign Keys
10 questions · exam conditions
0:00
Primary And Foreign KeysQuestion 1 of 10

A hospital network combines patient data from several facilities. A medical-record number is unique only within its issuing facility. A national identifier may be missing or corrected, and patients may share or change email addresses. The network must retain a stable identity for each patient across all facilities.

Which primary-key design best satisfies the stated requirements?

Use the national identifier as the primary key and allow a temporary value when it is missing.
Use email address as the primary key and update dependent rows whenever an address changes.
Use a generated patient_id as the primary key and apply separate constraints to business identifiers where appropriate.
Use the medical-record number alone as the primary key because each facility already treats it as unique.
← Back to quizzes

SQL Quiz

SQL Quiz: Primary And Foreign Keys

Practice Primary And Foreign Keys 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 Primary And Foreign Keys, 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

A hospital network combines patient data from several facilities. A medical-record number is unique only within its issuing facility. A national identifier may be missing or corrected, and patients may share or change email addresses. The network must retain a stable identity for each patient across all facilities.

Which primary-key design best satisfies the stated requirements?

  1. Use the national identifier as the primary key and allow a temporary value when it is missing.
  2. Use email address as the primary key and update dependent rows whenever an address changes.
  3. Use a generated patient_id as the primary key and apply separate constraints to business identifiers where appropriate. (correct answer)
  4. Use the medical-record number alone as the primary key because each facility already treats it as unique.
Explanation: Whenever you see a database design question involving identity and stability, ask yourself: can this identifier ever change, be missing, or be duplicated across contexts? If yes, it's a poor primary key candidate. A surrogate key — a system-generated value like a patient_id (often an auto-incrementing integer or UUID) — exists purely to identify a row. It never changes, is never null, and carries no real-world meaning that could become outdated. That's exactly why C is correct. The generated patient_id gives every patient a stable, permanent anchor in the database. Business identifiers like the national ID or email can then live in separate columns with their own constraints (unique, nullable, etc.), reflecting their real-world imperfections without threatening referential integrity. A fails because national identifiers can be missing or corrected. Using a "temporary value" as a placeholder means your primary key isn't truly stable — rows may need to be rekeyed when the real value arrives, cascading updates across every foreign key that references it. Primary keys should never need to change. B is similarly fragile. Email addresses change frequently, and the passage explicitly warns that patients may share or change them. Updating a primary key requires cascading that change to all dependent (child) rows, which is error-prone and expensive — and still doesn't solve the shared-address problem. D ignores the passage's direct statement that medical-record numbers are only unique within a facility. Across multiple facilities, duplicates are guaranteed, making this key meaningless at the network level. The study tip here: when a question describes real-world identifiers with any instability (nulls, changes, scope limits), the answer almost always involves a surrogate key.

Question 2

A training system stores Student(student_id, ...) and Course(course_id, ...). A student may take many courses, and a course may have many students. Under current rules, a student may enroll in a particular course only once.

Which design for Enrollment most directly enforces the relationship and the no-duplicate-enrollment rule?

  1. Make student_id the primary key and define course_id as a foreign key to Course.
  2. Make course_id the primary key and define student_id as a foreign key to Student.
  3. Use a generated enrollment_id primary key without any uniqueness constraint on the student-course pair.
  4. Use (student_id, course_id) as the primary key, with each column also referencing its parent table. (correct answer)
Explanation: When designing a junction table for a many-to-many relationship, you need to think about two things simultaneously: enforcing referential integrity (each enrollment must point to real students and courses) and enforcing business rules (no duplicate enrollments). The best design handles both in a single, elegant structure. A composite primary key of (student_id, course_id) does exactly this. Because primary keys are inherently unique and non-null, the pair can never appear twice — meaning a student literally cannot enroll in the same course more than once. Declaring each column as a foreign key then ties them back to their parent tables, ensuring no orphaned records exist. This makes D the correct answer: it enforces the many-to-many relationship and the no-duplicate rule simultaneously, with no extra constraints needed. Option A makes student_id alone the primary key, which means each student could only ever enroll in one course total — completely breaking the many-to-many relationship. Option B makes the same mistake in reverse: course_id as the sole primary key would allow each course to have only one student. Both A and B confuse which column should be a key versus a foreign key. Option C uses a surrogate enrollment_id key, which is sometimes valid, but without a separate UNIQUE constraint on (student_id, course_id), nothing prevents a student from enrolling in the same course multiple times — directly violating the stated business rule. A useful pattern to remember: whenever a junction table has a natural uniqueness rule built into its relationship (like "one enrollment per student-course pair"), prefer a composite primary key over a surrogate key — it enforces the rule for free.

Question 3

An order contains numbered lines. Line numbers begin at 1 for each order, so many orders can have a line numbered 1. Each line belongs to exactly one order, and deleting an order must not leave lines referring to a nonexistent order.

Which key definition correctly models OrderLine?

  1. Use line_number as the primary key and store order_id as a non-key descriptive attribute.
  2. Use (order_id, line_number) as the primary key, with order_id also a foreign key to Order. (correct answer)
  3. Use order_id as the primary key and make line_number a foreign key to the parent order.
  4. Use (order_id, line_number) as a foreign key to Order, whose primary key remains order_id alone.
Explanation: When modeling a detail table that belongs to a parent, you need to ask two questions: what uniquely identifies each row, and how do you enforce the relationship to the parent? In OrderLine, a line number alone doesn't identify a row — the same line number 1 appears in every order. You need both the order and the line number together to pinpoint a specific line. That's exactly what option B provides. By defining (order_id, line_number) as a composite primary key, you guarantee uniqueness at the row level: no two lines within the same order can share a number, yet different orders can reuse numbers freely. The additional FOREIGN KEY (order_id) REFERENCES Order(order_id) constraint enforces the passage's rule that deleting an order cannot leave orphaned lines — the database will either block the deletion or cascade it, depending on configuration. Option A fails immediately because line_number alone is not unique across orders — thousands of rows could share line_number = 1. Treating order_id as just a descriptive column also throws away referential integrity entirely. Option C gets things backwards: order_id is not unique per line (many lines share the same order), so it cannot be the primary key, and line_number has no meaningful target to reference as a foreign key. Option D misunderstands foreign key mechanics — a foreign key in OrderLine must reference the primary key of Order, but you cannot make a composite column pair in the child table reference a single-column primary key in the parent; the structure doesn't match. A useful rule of thumb: whenever a child table's rows are only meaningful within a parent context, the parent's key almost always belongs inside a composite primary key — not just as a loose attribute.

Question 4

A hosted application stores data for multiple tenants. Customer numbers are assigned independently within each tenant, so customer 125 may exist in several tenants. Every order belongs to one tenant and must reference a customer from that same tenant.

Which design most directly prevents an order from referencing another tenant's customer?

  1. Give Customer the key (tenant_id, customer_no) and make the same pair in Order a composite foreign key. (correct answer)
  2. Make customer_no alone the customer primary key and store tenant_id independently in both tables.
  3. Reference Customer.customer_no from Order.customer_no and separately reference each table's tenant to Tenant.
  4. Give every order a generated primary key and omit a customer foreign key because tenant membership is already stored.
Explanation: When designing multi-tenant databases, the core challenge is ensuring that relationships between tables don't accidentally cross tenant boundaries. The question to ask yourself is: what mechanism actually enforces that an order's customer belongs to the same tenant? A composite foreign key is the direct, structural solution. When you give Customer a primary key of (tenant_id, customer_no) and declare the same pair as a foreign key in Order, the database engine guarantees that any referenced customer row shares the exact same tenant_id as the order. This is answer A, and it works because the constraint is enforced at the schema level — no application logic required. Answer B is the classic trap. Storing tenant_id in both tables independently doesn't create any relational link between them. Nothing prevents Order.tenant_id = 1 from referencing a customer who belongs to tenant_id = 2. The two columns are just data; they don't enforce each other. Answer C makes a similar mistake. Referencing each table separately back to a Tenant table confirms that both rows have a tenant, but it doesn't guarantee they have the same tenant. It's a weaker constraint that leaves the cross-tenant gap wide open. Answer D is the worst option — it explicitly removes the foreign key entirely. A generated primary key on Order solves nothing about tenant isolation; omitting the customer reference just means the violation can never be detected at all. The study tip here: whenever a scenario involves multi-part uniqueness (like customer numbers that repeat across tenants), that's your signal to reach for a composite key and propagate it as a composite foreign key — don't split what logically belongs together.

Question 5

Each purchase request may optionally name one preferred supplier. A request can exist before any supplier is chosen. When a supplier is recorded, it must identify an existing row in Supplier(supplier_id, ...).

Which design represents both optionality and referential integrity most accurately?

  1. Add a nullable supplier_id foreign key in PurchaseRequest that references Supplier.supplier_id. (correct answer)
  2. Add a non-null supplier_id foreign key and use 0 whenever no supplier has been selected.
  3. Add request_id to Supplier as its primary key so every request can locate a supplier.
  4. Store a nullable supplier name in PurchaseRequest without defining a supplier foreign key.
Explanation: When designing a relationship where one entity optionally references another, you need to satisfy two separate requirements simultaneously: the column must allow absence (optionality), and when a value is present, it must point to a real row in the referenced table (referential integrity). Keeping both goals in mind helps you evaluate each option clearly. A nullable foreign key does exactly this. By allowing NULL, the column signals "no supplier chosen yet," satisfying optionality. By declaring it as a foreign key referencing Supplier.supplier_id, the database enforces that any non-null value must match an existing supplier row. Option A captures both requirements cleanly and is the correct design. Option B breaks optionality by making the column non-null, then uses the sentinel value 0 as a workaround. This is an anti-pattern — it forces Supplier to contain a fake "no supplier" row, pollutes your data, and complicates queries. Databases have NULL precisely to avoid this kind of hack. Option C inverts the relationship entirely. Adding request_id to Supplier as its primary key would mean every supplier is defined by a request, which contradicts the passage — suppliers exist independently, and requests optionally reference them. Option D uses a nullable supplier name instead of a foreign key. While this handles optionality, storing a name without a foreign key constraint means there's nothing preventing typos or references to nonexistent suppliers, so referential integrity is completely lost. A useful rule of thumb: when a relationship is optional but must be valid when present, reach for a nullable foreign key — it's the standard SQL pattern for exactly this scenario.

Question 6

A retailer classifies products by region-specific categories. Category code HOME may exist in several regions, but (region_code, category_code) is unique in Category. A product must be assigned to a category in the same region as the product.

Which relationship definition enforces the complete business rule?

  1. Reference Category.category_code from Product.category_code and validate the product region separately.
  2. Use (region_code, category_code) in Product as a composite foreign key to the matching Category key. (correct answer)
  3. Reference Category.region_code from Product.region_code and treat category code as descriptive text.
  4. Give Product a generated primary key and make category code unique across all product rows.
Explanation: When a business rule involves two columns together forming a unique identifier, your foreign key must reference both columns together — this is the core concept being tested here. Because (region_code, category_code) is the unique key in Category, a product's category assignment is only meaningful when both pieces are matched simultaneously. If you only reference category_code, you could accidentally link a product in region "WEST" to a HOME category that belongs to region "EAST" — violating the rule that a product must belong to a category in its same region. Option B solves this cleanly: by declaring a composite foreign key on (region_code, category_code) in Product pointing to the matching composite key in Category, the database automatically enforces both constraints in a single relationship. This is the correct answer. Option A fails because referencing only category_code ignores the regional dimension entirely. "Validating separately" means you're relying on application logic instead of database constraints — a fragile approach that can be bypassed. Option C reverses the logic dangerously: referencing only region_code leaves category_code as free text with no referential integrity, meaning any arbitrary category string could be inserted. Option D introduces a surrogate key on Product, which has nothing to do with enforcing the category-region relationship — it sidesteps the problem rather than solving it. The study tip here: whenever you see a uniqueness constraint spanning multiple columns, your foreign key referencing that table must mirror that exact composite structure. A partial foreign key reference is like a partial address — it doesn't get you to the right place.

Question 7

A manufacturer retains multiple approved versions of each product specification. ProductVersion is uniquely identified by (product_id, version_no). An inspection record must identify the exact specification version used, even after newer versions are approved.

Which foreign-key design for Inspection preserves this requirement?

  1. Store product_id only and reference the current row for that product at query time.
  2. Store version_no only and reference every specification having that version number.
  3. Store both product_id and version_no as one composite foreign key to ProductVersion. (correct answer)
  4. Store both columns but define separate foreign keys from each column to ProductVersion.
Explanation: When designing foreign keys that reference a composite primary key, the foreign key must mirror the entire primary key — not just part of it. Since ProductVersion is uniquely identified by the combination of (product_id, version_no), any reference to it must include both columns together as a single composite foreign key. This is exactly what the passage is testing. Option C is correct because defining a composite foreign key on (product_id, version_no) together forces each Inspection row to point to one specific, immutable version of a specification. The database engine will enforce that the exact pair exists in ProductVersion, guaranteeing historical accuracy even as newer versions are added. Option A fails because product_id alone doesn't identify a unique row in ProductVersion — it matches every version of that product. Querying "the current row" introduces ambiguity and breaks the historical record requirement entirely. Option B has the mirror problem: version_no alone could match version 2 of many different products, so it references the wrong set of rows and violates referential integrity. Option D is a subtle trap — defining separate foreign keys on each column individually doesn't enforce that the combination points to a valid row. You could end up with a product_id from one product and a version_no from a completely different product's specification, which is meaningless. A useful rule of thumb: when a table has a composite primary key, any child table referencing it needs a composite foreign key using all of those columns declared as a single constraint. Splitting them into separate foreign keys or omitting columns breaks the integrity guarantee.

Question 8

A publishing platform allows comments on either articles or photos. Every article and photo is a kind of content item, and a comment must reference exactly one existing content item. The database must enforce this relationship with foreign keys rather than relying on application-only checks.

Which conceptual key design best supports the requirement?

  1. Store target_type and target_id in Comment, with target_id serving as a foreign key that alternately references Article or Photo depending on the type column.
  2. Add comment_id as a foreign key in both Article and Photo, allowing either parent table to claim ownership of a given comment.
  3. Store nullable article_id and photo_id foreign keys in Comment, each referencing its respective parent table, with no additional constraint relating the two columns to each other.
  4. Create Content(content_id); make each article or photo ID a primary-key and foreign-key to Content, and reference Content.content_id from Comment. (correct answer)
Explanation: When a single table needs to relate to multiple parent tables, you're dealing with a polymorphic association problem. The key question is: can SQL's foreign key mechanism actually enforce the relationship? Standard foreign keys must point to one specific table — they can't conditionally reference different tables based on a column value. This is exactly why D is the correct design. By introducing a Content supertable with a shared content_id, every article and photo inherits a content identity. You make each article's and photo's primary key also a foreign key back to Content — this is the classic supertype/subtype (or table inheritance) pattern. Then Comment simply holds a foreign key to Content.content_id, which is always a valid, enforceable reference. The database guarantees referential integrity without any application-side logic. A is tempting but fundamentally broken at the database level. A column can't serve as a foreign key to two different tables simultaneously — SQL has no syntax for "foreign key to either Table A or Table B." You'd lose all DB-enforced referential integrity, relying solely on application checks. B reverses the relationship incorrectly. Putting comment_id as a foreign key in Article and Photo means a comment could be claimed by multiple parents, and it complicates querying which content owns which comments. It doesn't reflect how the data actually flows. C is a partial solution that creates a data-integrity gap. With two nullable foreign keys and no constraint tying them together, nothing stops a comment from having both set, neither set, or pointing to a deleted row in one table while the other is null. When you see polymorphic relationships in exam questions, ask yourself: can a standard foreign key enforce this? If not, look for a supertable design — that's almost always the cleanest, DB-enforced answer.

Question 9

An organizational hierarchy is stored in Employee. Every employee has an employee_id. Most employees report to one manager, managers are also employees, and the chief executive has no manager.

Which key relationship best models the hierarchy without requiring a separate manager entity?

  1. Make manager_id the primary key and define employee_id as a foreign key to manager_id.
  2. Keep employee_id as the primary key and make nullable manager_id a foreign key to Employee.employee_id. (correct answer)
  3. Make (employee_id, manager_id) the primary key and require both components to be non-null.
  4. Keep employee_id as the primary key and make manager_id a foreign key to a separate Department table.
Explanation: When modeling a hierarchy within a single table, the key concept to recognize is the self-referencing foreign key — a foreign key that points back to the same table's primary key. This pattern lets you represent parent-child relationships (like manager-to-employee) without introducing additional tables. Option B is the correct design. By keeping employee_id as the primary key, each employee has a unique identifier. Making manager_id a nullable foreign key that references Employee.employee_id elegantly captures the reporting relationship: most employees have a manager (non-null manager_id), while the CEO has no manager (null manager_id). This single table handles every level of the hierarchy recursively. Option A inverts the logic entirely. Making manager_id the primary key would mean managers — not employees — define the table's identity, which breaks for the CEO (who has no manager) and misrepresents what the table is actually tracking. Option C creates a composite primary key requiring both employee_id and manager_id to be non-null. This immediately fails for the CEO, who has no manager, and forces the awkward assumption that an employee's identity depends on their reporting relationship. Option D introduces a separate Department table for manager_id, which is not what manager_id represents. A manager is an employee, not a department — pointing to the wrong table violates referential integrity and the spirit of the hierarchy. The takeaway: whenever you see a hierarchy stored in one table, think self-referencing foreign key with a nullable column to handle the root node. That nullable field is the telltale sign of a well-modeled recursive relationship.

Question 10

Employees and lockers have an optional one-to-one relationship: an employee may have no locker or one locker, and a locker may be unassigned or assigned to one employee. The design uses a separate LockerAssignment table.

Which key arrangement enforces the required cardinality in LockerAssignment?

  1. Use employee_id as the primary key and locker_id as a unique foreign key, with both columns referencing their parent tables. (correct answer)
  2. Use a generated assignment key and define ordinary, non-unique foreign keys for both employee and locker.
  3. Use (employee_id, locker_id) as the primary key and define no other uniqueness constraints.
  4. Use locker_id as the primary key and leave employee_id as a non-key column without a foreign key.
Explanation: When modeling a one-to-one relationship in SQL, your job is to ensure that neither side of the relationship can appear more than once in the junction table. The tool for this is uniqueness constraints — specifically, combining primary keys and unique constraints on foreign keys. Answer A is correct because using employee_id as the primary key automatically guarantees no employee appears twice. Making locker_id a unique foreign key guarantees no locker appears twice. Together, these two constraints enforce true one-to-one cardinality: each employee maps to at most one locker, and each locker maps to at most one employee. The "optional" part is handled by allowing NULLs or simply omitting a row — no assignment row means no assignment. Answer B fails because ordinary, non-unique foreign keys place no restriction on repetition. The same employee could be assigned five lockers, and the same locker assigned to three employees — the database wouldn't complain. Answer C uses a composite primary key (employee_id, locker_id), which prevents duplicate pairs but not duplicates on individual columns. An employee could still appear in multiple rows paired with different lockers, violating one-to-one cardinality. Answer D is a structural disaster: it makes locker_id the primary key but leaves employee_id without a foreign key constraint, meaning referential integrity is completely unenforced. Nothing stops orphaned or duplicate employee references. Study tip: When you see a one-to-one relationship question, immediately ask yourself: "Where are the uniqueness constraints, and do they cover both sides?" A foreign key alone doesn't enforce uniqueness — you need either a primary key or a UNIQUE constraint on that column too.