Library card numbers must never collide across city branches. Which constraint should enforce that rule on patron_card_number?
Select an answer to reveal the explanation.
Short Explanation
You can prevent two branches from issuing the same card number with a UNIQUE constraint on patron_card_number. A length check or a default that repeats one literal won’t prevent collisions, and a foreign key to a temporary staging table doesn’t enforce uniqueness on the live column.
Full Explanation
Library card numbers must not collide across city branches, especially when branches share a central database. A UNIQUE constraint on patron_card_number directly enforces that rule when a card number is inserted or updated, regardless of which branch creates the card. It is the integrity mechanism that prevents duplicate values in this identifier column. A FOREIGN KEY from patron_card_number to a temporary staging table instead requires a matching value in that table; it does not ensure that card numbers in the live column are unique across branches, and the staging table may be temporary. A DEFAULT that assigns the same literal to every new card would create repeated values rather than prevent collisions. A CHECK constraint that only requires more than three characters validates a limited format condition, but two branches could still issue the same qualifying number. Exam caveat: in SQL Server, a UNIQUE constraint allows a single NULL, so uniqueness alone does not ensure every card has a number. Practical check: insert a card number for one branch, then try to insert that same patron_card_number for another branch and verify that the database rejects the duplicate with a unique constraint violation.