How does UNIQUE (team_id, jersey_no) differ from separate UNIQUE (team_id) and UNIQUE (jersey_no) declarations?

## What each declaration asserts Uniqueness is a statement about a *column list*, and the whole list is the unit. `UNIQUE (team_id, jersey_no)` says: taking the two values together as a tuple, no two rows may produce the same tuple. Nothing is claimed about either column on its own. ```sql CREATE TABLE roster ( player_id BIGINT NOT NULL, team_id INTEGER NOT NULL, jersey_no INTEGER NOT NULL, CONSTRAINT pk_roster PRIMARY KEY (player_id), CONSTRAINT uq_roster_jersey UNIQUE (team_id, jersey_no) ); ``` Against that table: ```sql INSERT INTO roster VALUES (1, 7, 10); -- ok INSERT INTO roster VALUES (2, 7, 11); -- ok: same team, different number INSERT INTO roster VALUES (3, 8, 10); -- ok: same number, different team INSERT INTO roster VALUES (4, 7, 10); -- rejected: pair (7, 10) repeats ``` Swap the declaration for two single-column constraints and only the first insert survives: ```sql CONSTRAINT uq_roster_team UNIQUE (team_id), CONSTRAINT uq_roster_jersey UNIQUE (jersey_no) ``` Row 2 is now rejected because team 7 already exists; row 3 is rejected because jersey 10 already exists. The table can hold at most one player per team and at most one player per jersey number in the entire league — almost never what anyone wanted. ## Recognising which one the requirement asks for Read the business rule and count the scopes. "A jersey number is unique **within a team**" has a scope column (`team_id`) and a value column (`jersey_no`) — that is a composite unique key over both. "Each employee has at most one badge number, and no badge number is reused" is genuinely two independent rules and does want two constraints. The phrase "per" or "within" in a requirement is a strong signal that the scope column belongs inside the same unique key. The same reasoning extends past two columns: `UNIQUE (tenant_id, project_id, slug)` says a slug is unique within a project within a tenant. ## Syntax notes A composite unique key has no inline form and must be a table constraint. A single-column unique key may be written either way: ```sql email VARCHAR(320) NOT NULL UNIQUE, -- inline CONSTRAINT uq_users_email UNIQUE (email) -- table level, named ``` A table may declare as many unique constraints as the model needs — one per rule — which is a real difference from `PRIMARY KEY`, of which there can be at most one. Another difference: `PRIMARY KEY` makes its columns non-nullable as part of the declaration, while `UNIQUE` says nothing about nullability. If the columns of a unique key must always be present, declare `NOT NULL` on them yourself. ## Does the order of the columns matter? For **which rows the constraint rejects**, no. `UNIQUE (team_id, jersey_no)` and `UNIQUE (jersey_no, team_id)` accept and reject exactly the same rows, because tuple equality does not care in which order the components were listed. Column order in the declaration does affect the structure the engine builds behind the constraint and therefore which lookups it can help with — but that is an indexing decision, not a change to the rule being declared. State the rule in the order that reads naturally (scope column first is a common convention) and treat access paths separately. ## Interaction with foreign keys A composite unique key is a legal target for a foreign key: a child table can declare `FOREIGN KEY (team_id, jersey_no) REFERENCES roster (team_id, jersey_no)`, matching the full column list in order. A foreign key can never reference only part of a unique key, so if other tables need to point at a row, decide up front whether they should reference the composite key or a single surrogate key column. ## Choosing between composite unique and composite primary key If the pair genuinely identifies the row and nothing else does, `PRIMARY KEY (team_id, jersey_no)` says so directly. Many schemas instead keep a surrogate `player_id` as the primary key and declare the business rule separately as a unique constraint — that is the shape shown above, and it keeps the identifying key stable even when a player changes shirt number.