SQL Quiz: Identifying Keys
10 questions · exam conditions
0:00
Identifying KeysQuestion 1 of 10

A database contains Account(AccountID, Username, CreatedAt). AccountID is the primary key, and Username has declared UNIQUE and NOT NULL constraints. A new relation, LoginAudit(AuditID, SubmittedUsername, AttemptedAt), must associate an audit row with the account having the submitted username.

Which statement correctly classifies LoginAudit.SubmittedUsername if the relationship is enforced against Account.Username?

It can be a foreign key because a foreign key may reference a declared unique candidate key rather than only a primary key.
It must become the primary key of LoginAudit because it references a unique value in the parent relation.
It cannot be a foreign key because only Account.AccountID, as the declared primary key, may be referenced.
It is merely a copied attribute because foreign-key columns must have the same column name as the referenced columns.
← Back to quizzes

SQL Quiz

SQL Quiz: Identifying Keys

Practice Identifying 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 Identifying 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 database contains Account(AccountID, Username, CreatedAt). AccountID is the primary key, and Username has declared UNIQUE and NOT NULL constraints. A new relation, LoginAudit(AuditID, SubmittedUsername, AttemptedAt), must associate an audit row with the account having the submitted username.

Which statement correctly classifies LoginAudit.SubmittedUsername if the relationship is enforced against Account.Username?

  1. It can be a foreign key because a foreign key may reference a declared unique candidate key rather than only a primary key. (correct answer)
  2. It must become the primary key of LoginAudit because it references a unique value in the parent relation.
  3. It cannot be a foreign key because only Account.AccountID, as the declared primary key, may be referenced.
  4. It is merely a copied attribute because foreign-key columns must have the same column name as the referenced columns.
Explanation: When a question asks whether a column can serve as a foreign key, your first instinct should be to ask: what can a foreign key actually reference? Many students memorize that foreign keys point to primary keys — but that's only part of the rule. In standard SQL, a foreign key may reference any column (or set of columns) that has a UNIQUE constraint, not just the primary key. A primary key is simply a special case of a candidate key — one that is both unique and not null. Account.Username carries both UNIQUE and NOT NULL, which makes it a valid candidate key. Therefore, LoginAudit.SubmittedUsername can legally declare a foreign key constraint referencing Account.Username. Answer A captures this precisely: foreign keys may reference any declared unique candidate key. B is wrong because referencing a unique column in a parent table doesn't force SubmittedUsername to become LoginAudit's primary key. The two concepts are independent — a foreign key column is just a referencing column, not necessarily an identifier of its own table. C reflects the common misconception that only the primary key can be referenced. This is incorrect per SQL standards; any uniquely constrained column is fair game. D is wrong on two counts: column names don't need to match between referencing and referenced tables, and a properly defined foreign key constraint is far more than a "copied attribute" — it's an enforced referential integrity rule. Study tip: Remember that PRIMARY KEY = NOT NULL + UNIQUE, so any UNIQUE NOT NULL column qualifies as a candidate key and is a valid foreign-key target.

Question 2

An employee may be unassigned while awaiting placement. The relations are Department(DepartmentID, DepartmentName) and Employee(EmployeeID, DepartmentID, FullName). Every non-null employee department value must identify an existing department.

Which classification best fits Employee.DepartmentID?

  1. It is a nullable foreign key; null represents no department, while each non-null value must match a department key. (correct answer)
  2. It is not a foreign key because any column participating in a foreign key must be declared non-null.
  3. It is a nullable primary key because it identifies the department associated with each individual employee.
  4. It is both the employee primary key and a department foreign key because it links the two relations.
Explanation: When you see a question about foreign keys, ask yourself two things: does this column reference another table's primary key, and must it always have a value? These are independent questions — a foreign key can absolutely be nullable. Employee.DepartmentID references Department.DepartmentID, making it a foreign key by definition. The passage also tells you an employee can be unassigned, meaning the column has no required value — it's nullable. When a value is present, referential integrity requires it to match an existing department. That's exactly what A describes, making it the correct answer. B is wrong because it states a false rule. SQL standards and every major database system allow nullable foreign keys. A NULL in a foreign key column simply means "no reference exists," which doesn't violate referential integrity — it's not pointing to a nonexistent row, it's pointing nowhere. C confuses "foreign key" with "primary key." A primary key uniquely identifies rows within its own table. Employee.DepartmentID doesn't identify individual employees — multiple employees can share the same department, or have NULL. It identifies an associated department, which is the definition of a foreign key. D is doubly wrong. The employee's primary key is EmployeeID, not DepartmentID. A single column can serve dual roles in some schemas, but here DepartmentID clearly isn't what makes each employee row unique. Study tip: Remember that NULL means "unknown or inapplicable," not "invalid." A nullable foreign key is a common, legitimate design pattern for optional relationships — don't let answer choices like B trick you into thinking nullability disqualifies a column from being a foreign key.

Question 3

A customer may save several addresses. Each sales order records both the billing address and the shipping address, which may be the same or different. The relations include Address(AddressID, CustomerID, AddressText) and SalesOrder(OrderID, BillingAddressID, ShippingAddressID).

How should the address-related columns in SalesOrder be classified?

  1. Together they form one composite foreign key to Address, because both values describe the address information for one order.
  2. Each is a separate foreign key to Address.AddressID, because the columns represent two roles in the same parent relation. (correct answer)
  3. Only ShippingAddressID is a foreign key; BillingAddressID must instead be part of the order's primary key.
  4. Both are candidate primary keys of SalesOrder, because each value individually identifies an existing address row.
Explanation: When a column in one table stores a value that must match a primary key in another table, it's a foreign key. The key insight here is that a single parent table can be referenced multiple times by different columns in a child table — each reference is its own independent foreign key, playing a distinct role. In SalesOrder, both BillingAddressID and ShippingAddressID reference Address.AddressID. They are separate foreign keys that happen to point to the same parent table, but they serve different semantic roles: one identifies where the bill goes, the other where the package ships. This makes B correct — each column is an independent foreign key to Address.AddressID. A is wrong because a composite foreign key means multiple columns together form a single reference to a multi-column primary key — like (City, ZipCode) jointly referencing a composite PK. Here, Address.AddressID is a single column, so there's no composite key situation. The two address columns don't combine into one reference; they are two separate references. C is incorrect because there's no rule requiring billing information to be part of a primary key. BillingAddressID is simply another attribute that references an address row — it has no special obligation to be a PK component. D confuses the concept entirely. A foreign key points to a row in another table; it doesn't make the referencing column a candidate key of the current table. An order isn't uniquely identified by an address ID. Study tip: Whenever you see two columns in the same table referencing the same parent table, recognize them as separate foreign keys with different roles — not a composite key.

Question 4

A geographic database stores Country(CountryID, ISOCode, Name) and Airport(AirportID, CountryID, AirportName). CountryID is permanently assigned and never changes. ISOCode is currently unique, but codes may be corrected or reassigned under future policy changes. Airport records must continue to identify the same country after such changes.

Which key design best satisfies the stated requirements?

  1. Use (CountryID, ISOCode) as the country primary key and reference both columns from every airport to preserve all identifiers.
  2. Use ISOCode as the country primary key and copy it into each airport row, because recognizable values are always better identifiers.
  3. Use CountryID as the country primary key and as the referenced value for the airport foreign key; constrain ISOCode separately as needed. (correct answer)
  4. Use AirportID as a foreign key to Country, because its permanent value can indirectly identify the airport's country.
Explanation: When designing relational schemas, the key question is: which identifier remains stable over time and can safely anchor foreign key relationships? A primary key should be permanent and immutable, while other unique attributes can be constrained separately without carrying the full burden of referential integrity. CountryID fits this role perfectly. The passage explicitly states it is permanently assigned and never changes — exactly what you want from a primary key. Airport records reference CountryID as a foreign key, so even if ISOCode gets corrected or reassigned in the future, the airport-country relationship remains intact. ISOCode can still be declared UNIQUE or given its own constraint for lookup purposes without being the referenced key. This is option C, and it correctly separates the concerns of identification from human-readable labeling. Option A is tempting but introduces unnecessary complexity. A composite primary key of (CountryID, ISOCode) means every airport row must store both columns as a foreign key — and if ISOCode changes, you face cascading updates across all airport records, which defeats the purpose of having a stable identifier. Option B is a classic trap: using a recognizable, human-friendly code as a primary key feels intuitive, but the passage explicitly warns that ISOCode can change. Basing referential integrity on a mutable value is a schema design anti-pattern that leads to update anomalies. Option D confuses direction — a foreign key points from a child table to a parent, not the other way around. AirportID identifying a country makes no logical sense. Study tip: When a question mentions that one attribute is "permanent" and another "may change," that's a direct signal — use the permanent attribute as your primary key and foreign key reference.

Question 5

A data profiler examines a relation with columns A, B, and C. In the current rows, A contains no duplicates or nulls, B contains duplicates, and C contains no duplicates but has one null. No primary-key declaration, uniqueness constraint, or business rule is available.

Which conclusion about the relation's primary key is justified by this information alone?

  1. A is necessarily the primary key because its current values are unique and non-null, satisfying both requirements for a declared primary key.
  2. C is necessarily the primary key because its values are currently unique, and the single null will be corrected once the schema is finalized.
  3. (B, C) is necessarily the primary key because combining columns with partial uniqueness produces a composite identifier guaranteed to be unique.
  4. No primary key can be identified with certainty; observed data patterns in a sample do not establish a declared or durable constraint on future values. (correct answer)
Explanation: When working with SQL and relational database theory, it's critical to distinguish between observed data patterns and declared schema constraints. A primary key isn't something you infer from a snapshot of data — it's a formal declaration that guarantees uniqueness and non-nullability for every row, past, present, and future. That distinction makes D the only defensible answer. Even if a column looks unique and non-null right now, nothing prevents a future INSERT from violating that pattern unless a constraint enforces it. Without a declared primary key or uniqueness constraint, you simply cannot conclude that any column or combination of columns is the primary key. A is tempting but wrong. Observing that A currently has no duplicates or nulls is a fact about today's data, not a guarantee about tomorrow's. A column can satisfy primary key criteria in a snapshot without being declared as one. Calling it "necessarily the primary key" overstates what the data alone can tell you. B is doubly flawed. C already contains a null, which immediately disqualifies it from being a primary key under SQL rules (primary keys must be entirely non-null). Assuming the null "will be corrected later" introduces speculation about future schema decisions — which is exactly the kind of assumption this question warns against. C is also unjustified. Combining columns doesn't automatically create a reliable composite key. Without testing all combinations and, crucially, without a declared constraint, you cannot guarantee (B, C) will remain unique as data changes. Study tip: On SQL exam questions about keys, always ask: Is this a declared constraint or just an observation? Data can lie — constraints enforce truth.

Question 6

Within an order, line numbers begin at 1 and may be reused in other orders. Each order line refers to one product. The relations are SalesOrder(OrderID, OrderDate), Product(ProductID, ProductName), and OrderLine(OrderID, LineNumber, ProductID, Quantity).

Which statement correctly identifies the keys of OrderLine?

  1. ProductID is the primary key; OrderID is a foreign key, while LineNumber is never part of any key.
  2. LineNumber is the primary key; (OrderID, ProductID) is one composite foreign key identifying the order's product.
  3. (OrderID, LineNumber) is the primary key; OrderID and ProductID are foreign keys to their respective parent relations. (correct answer)
  4. (OrderID, ProductID) is the primary key; LineNumber is a foreign key that references the sales order.
Explanation: When working with keys in a relational database, you need to ask two questions: what combination of columns uniquely identifies each row, and which columns reference primary keys in other tables? For OrderLine, think carefully about what makes a row unique. The passage tells you line numbers restart at 1 for each new order — so LineNumber alone can't uniquely identify a row (multiple orders each have a Line 1). However, combining OrderID with LineNumber gives you a unique identifier: within a single order, line numbers are distinct. That composite pair (OrderID, LineNumber) is the primary key. Additionally, OrderID references SalesOrder(OrderID), making it a foreign key, and ProductID references Product(ProductID), making it a foreign key as well. This confirms C as correct. A is wrong because ProductID alone cannot be a primary key — many order lines can reference the same product. Designating LineNumber as "never part of any key" also ignores its essential role in the composite primary key. B fails because LineNumber alone is not unique across orders. The claim that (OrderID, ProductID) forms a composite foreign key is also a category error — those are two separate foreign keys pointing to two different parent tables, not one combined key. D incorrectly proposes (OrderID, ProductID) as the primary key. A customer could order the same product on multiple lines, so this pair isn't necessarily unique. Claiming LineNumber is a foreign key is nonsensical — there's no parent table it could reference. A good study habit: when you see a junction or detail table, always check whether uniqueness requires a composite primary key before assuming any single column qualifies.

Question 7

The relation Employee(EmployeeID, FullName, ManagerID) stores an organization's reporting structure. EmployeeID uniquely identifies an employee. A top-level executive has no manager; every other ManagerID must identify another employee.

Which key interpretation correctly models the reporting relationship?

  1. EmployeeID is the primary key, and nullable ManagerID is a foreign key referencing Employee.EmployeeID in the same relation. (correct answer)
  2. ManagerID is the primary key, and EmployeeID is a foreign key because each employee reports through a manager.
  3. (EmployeeID, ManagerID) is the primary key, and neither column is a foreign key because only one relation is involved.
  4. EmployeeID and ManagerID are both primary keys because each can contain identifiers assigned to employees.
Explanation: When a table references itself, you're dealing with a self-referencing foreign key — a powerful pattern for modeling hierarchies like org charts, category trees, or bill-of-materials structures. The key question to ask is: what uniquely identifies each row, and what column points back into the same table? In Employee, each employee has a unique EmployeeID, making it the natural primary key. ManagerID holds the EmployeeID of another employee — or NULL for top-level executives who have no manager. Because ManagerID points to a value in the same table's primary key column, it qualifies as a foreign key referencing Employee.EmployeeID within the same relation. Allowing NULL is essential here, since not every employee has a manager. This is exactly what A describes, making it correct. B has the logic inverted — managers are themselves employees, so ManagerID is not a primary key; it's a subordinate attribute that references the primary key. Making EmployeeID a foreign key in B makes no structural sense. C incorrectly promotes the composite (EmployeeID, ManagerID) to a primary key. A primary key must be minimal and unique per row. EmployeeID alone already uniquely identifies each employee, so adding ManagerID is redundant — and NULL values in a primary key are forbidden anyway. D confuses "contains employee identifiers" with "is a primary key." Having employee ID values doesn't grant primary key status; uniqueness and row identification do. Study tip: Whenever you see a hierarchy modeled in a single table, immediately look for a self-referencing foreign key — and remember that NULL in that foreign key simply means "no parent exists."

Question 8

Each user account may have at most one preferences row, and a preferences row cannot exist without its user account. The relations are UserAccount(UserID, Email) and UserPreferences(UserID, Theme, Language). The design must enforce the one-to-zero-or-one relationship using only primary-key and foreign-key constraints.

Which key assignment directly enforces the required relationship?

  1. Make (UserID, Theme) the preferences primary key and make only Theme a foreign key to the user relation.
  2. Make UserPreferences.UserID only a foreign key and use Theme as the preferences primary key.
  3. Make UserAccount.UserID a foreign key referencing UserPreferences.UserID and leave preferences without a primary key.
  4. Make UserPreferences.UserID its primary key and also a foreign key referencing UserAccount.UserID. (correct answer)
Explanation: When modeling a one-to-zero-or-one relationship in SQL, ask yourself two questions: "What prevents duplicate child rows?" and "What prevents orphaned child rows?" You need constraints that answer both simultaneously. The relationship here says each UserPreferences row must belong to exactly one UserAccount, and no user can have more than one preferences row. Making UserPreferences.UserID the primary key handles the first requirement — primary keys enforce uniqueness, so the same UserID can never appear twice in the preferences table, capping each user at one preferences row. Making that same UserID column a foreign key referencing UserAccount.UserID handles the second requirement — it guarantees no preferences row can exist without a matching user account. Answer D combines both constraints on the same column, cleanly enforcing the entire relationship with nothing extra needed. Answer A is wrong because a composite primary key (UserID, Theme) allows multiple preferences rows per user (different themes would create distinct composite keys), breaking the "at most one" rule entirely. Making only Theme a foreign key to the user table is also structurally nonsensical. Answer B is wrong because using Theme as the primary key means preferences rows are uniquely identified by theme, not by user — multiple users could share a theme, and a user could appear multiple times with different themes. Answer C reverses the relationship entirely. A UserAccount foreign key pointing to UserPreferences would mean a user account can't exist without preferences, which is the opposite of what's required. Remember: in a one-to-zero-or-one relationship, the child table's primary key is the foreign key — same column, both roles.

Question 9

A multitenant sales system assigns customer numbers independently within each tenant, so different tenants may both have customer number 1001. Each order has a globally unique OrderID and belongs to exactly one customer. The relations are Tenant(TenantID, Name), Customer(TenantID, CustomerNumber, Name), and SalesOrder(OrderID, TenantID, CustomerNumber, OrderDate).

Which key assignment correctly represents these identification rules?

  1. Customer has primary key (TenantID, CustomerNumber); SalesOrder has primary key OrderID; and its customer columns form a composite foreign key. (correct answer)
  2. Customer has primary key CustomerNumber; SalesOrder has primary key OrderID; and only CustomerNumber is a foreign key.
  3. Customer has primary key TenantID; SalesOrder has primary key (OrderID, TenantID); and CustomerNumber is a foreign key.
  4. Customer has primary key (TenantID, CustomerNumber); SalesOrder has primary key (TenantID, CustomerNumber); and OrderID is a foreign key.
Explanation: When designing keys in a multitenant database, ask yourself two questions: "What combination of attributes makes each row unique?" and "Do my foreign keys reference the entire primary key of the parent table?" In this schema, CustomerNumber alone doesn't uniquely identify a customer — two different tenants can both have customer 1001. Uniqueness only emerges when you pair TenantID with CustomerNumber. So Customer's primary key must be the composite (TenantID, CustomerNumber). Since SalesOrder already has a globally unique OrderID, that single column is sufficient as its primary key. Finally, because SalesOrder references a Customer, its foreign key must mirror the full composite primary key — meaning both TenantID and CustomerNumber together form the foreign key. That's exactly what Answer A describes, making it correct. B fails because it uses CustomerNumber alone as Customer's primary key. This would incorrectly allow only one customer numbered 1001 across all tenants — violating the scenario's core premise. C makes TenantID the primary key of Customer, which is wrong on two counts: a tenant has many customers, so TenantID alone isn't unique per row, and it also makes SalesOrder's primary key a composite (OrderID, TenantID) when OrderID is already globally unique — unnecessarily inflating the key. D makes (TenantID, CustomerNumber) the primary key of SalesOrder, which conflates the customer identifier with the order identifier. Multiple orders per customer would be impossible, and OrderID demoted to a foreign key makes no logical sense. Study tip: Whenever you see "independently assigned within a scope," that scope column must be part of the primary key — and any referencing table's foreign key must include it too.

Question 10

A college identifies a course offering by CourseID, Term, and SectionNumber. A student may register once for a particular offering but may take the same course again in another term. The relations are Student(StudentID, Name), CourseOffering(CourseID, Term, SectionNumber), and Registration(StudentID, CourseID, Term, SectionNumber, Grade).

Which primary-key and foreign-key interpretation is correct for Registration?

  1. Its primary key is (StudentID, CourseID), while Term and SectionNumber are descriptive attributes rather than key columns.
  2. Its primary key is (StudentID, CourseID, Term, SectionNumber), with separate references to Student and the composite offering key. (correct answer)
  3. Its primary key is (CourseID, Term, SectionNumber), while StudentID is a foreign key that need not participate in identification.
  4. Its primary key is StudentID, with (CourseID, Term, SectionNumber) serving only as a composite foreign key.
Explanation: When designing a relational schema, you need to ask two questions for every table: "What combination of columns uniquely identifies each row?" and "Which columns reference other tables?" For Registration, think carefully about what a single row represents — one student enrolled in one specific course offering. A course offering is identified by the composite key (CourseID, Term, SectionNumber) — all three columns together. A student can retake the same course in a different term, meaning (StudentID, CourseID) alone cannot distinguish those two enrollments. The only combination that uniquely identifies a registration is (StudentID, CourseID, Term, SectionNumber). This four-column primary key also naturally decomposes into two foreign key references: StudentID references Student, and (CourseID, Term, SectionNumber) references CourseOffering. That makes B the correct interpretation. A is wrong because dropping Term and SectionNumber from the primary key means you can't distinguish a student taking the same course in two different terms — those rows would appear identical, violating uniqueness. C incorrectly uses only the offering key as the primary key, which would prevent more than one student from registering for the same offering — clearly wrong for a real enrollment system. D reduces the primary key to just StudentID, implying each student can only ever have one registration total, which contradicts the entire purpose of the table. A useful rule of thumb: in junction/association tables (tables that connect two or more entities), the primary key is almost always the combination of all the foreign keys it references. When one of those references is itself composite, every column in that composite must be included.