When a coding assistant proposes a uniqueness rule, test the business states it permits. A schema can create successfully, contain a constraint named “unique,” and still allow two active records for the same entity. Nullable columns deserve particular attention because their behavior inside unique keys depends on the database.
For a business application, this can mean two current equipment assignments, two preferred contact records or conflicting active subscriptions. Those are different policies, so the correct constraint starts with the intended rule rather than a familiar SQL pattern.
This guide develops a fictional equipment-assignment example. We executed its small, sequential database checks with Python 3.12.14 and SQLite 3.53.1. We did not test a coding assistant, PostgreSQL, a production migration or concurrent writers. The proposed review process is editorial guidance, not a measured claim about AI-generated error rates.
State what must be unique, and when
Suppose an application retains assignment history for equipment. The fictional owner specifies: within an organization, an asset may have at most one active assignment; ended assignments remain available as history. Here, and only for this example, ended_at IS NULL means active.
The organization is part of the identity. Asset asset-17 in org-a is different from the same local identifier in org-b. Both identity fields must be present. The end marker controls which rows participate in the rule.
“At most one” also matters. The rule permits an asset with no active assignment. If the workflow requires every available asset to have exactly one assignment, that is an additional obligation. A uniqueness rule cannot ensure that a row exists.
Ask the assistant to restate these distinctions before generating SQL. Require examples of both permitted and forbidden data. This gives the reviewer an expectation independent of whether the proposed code looks conventional.
See why the plausible composite key is insufficient
Consider this intentionally inadequate schema:
CREATE TABLE assignment_bad (
org_id TEXT NOT NULL,
asset_id TEXT NOT NULL,
ended_at TEXT,
UNIQUE (org_id, asset_id, ended_at)
);
INSERT INTO assignment_bad VALUES ('org-a', 'asset-17', NULL);
INSERT INTO assignment_bad VALUES ('org-a', 'asset-17', NULL);In our SQLite check, both inserts succeeded. The table contained two active rows for the same organization and asset. That violates the fictional policy even though the database behaved as documented.
SQLite’s CREATE INDEX documentation explains that NULL values are considered distinct for unique indexes. Placing the end marker inside the key therefore does not make two open-ended assignments collide. Adding a primary key would give the rows separate identifiers without fixing that business-state rule.
The same definition also constrains historical combinations of organization, asset and non-null end value. That may reject legitimate history while failing to reject the active duplicate. More columns in a unique key do not necessarily mean stronger protection of the intended policy.
Do not label this a database defect. The mistake is selecting a rule whose semantics differ from the requirement. Nor does this constructed example establish that a named assistant would generate it.
Restrict uniqueness to the participating rows
For this particular policy, the candidate below makes the participating subset explicit:
CREATE TABLE assignments (
id INTEGER PRIMARY KEY,
org_id TEXT NOT NULL,
asset_id TEXT NOT NULL,
ended_at TEXT
);
CREATE UNIQUE INDEX one_active_assignment
ON assignments (org_id, asset_id)
WHERE ended_at IS NULL;SQLite’s partial-index documentation describes indexes covering only rows that satisfy a predicate, with uniqueness enforceable within that subset. Here the indexed identity is organization plus asset; only active rows enter the index. Ended rows remain outside it.
In a separate database check using this schema, the first active assignment succeeded and a second for the same identity raised an integrity error. An active assignment for the same asset identifier in another organization succeeded. Two ended rows with the same identity and end value also succeeded.
That last observation is deliberate: our stated policy does not make historical end values unique. Real systems may need additional rules for assignment identity, valid dates or duplicate imported events. Decide those separately rather than accidentally enforcing them through the active-record constraint.
This schema is a teaching fixture. It omits foreign keys, date validation, assignee details and the rest of an equipment system. Its successful checks establish only the behavior exercised.
Test transitions as well as the first insert
A row can enter the active subset through an update. Test that route explicitly. In our fixture, attempting to reopen an ended assignment while another active assignment existed raised an integrity error. A missing organization identifier was also rejected by the separate NOT NULL requirement.
We then ended the current assignment and inserted a replacement. The sequence succeeded, leaving one active row for that organization and asset. These were sequential operations in an in-memory database; they do not demonstrate transaction recovery or concurrent application behavior.
Use a review table like this before approving generated schema changes:
| Proposed state or change | Expected result under this fictional policy |
|---|---|
| First active assignment for an identity | Allowed |
| Second active assignment for the same identity | Rejected |
| Same local asset ID in another organization | Allowed |
| Multiple ended assignments | Allowed by this rule |
| Reopen history while an active row exists | Rejected |
| Missing organization or asset identity | Rejected |
| No active assignment | Allowed; the rule is “at most one” |
Keep expectations attached to the business requirement. An assistant-generated test that merely reproduces the generated SQL’s behavior can approve the wrong design. Have the responsible reviewer confirm the table before using implementation output as evidence.
Also test application handling of rejection. The user should receive a meaningful conflict outcome rather than a success message followed by an absent assignment. That integration check remains to be performed on the actual application.
Verify the engine instead of translating syntax mechanically
PostgreSQL 18’s constraint documentation likewise describes default uniqueness behavior that treats nulls as distinct, and provides NULLS NOT DISTINCT as an alternative. It also describes partial unique indexes for restrictions over only some rows and warns that null treatment differs across implementations.
Those are documented capabilities, not PostgreSQL execution results from this article. A PostgreSQL-specific design could have several options, with different effects on historical rows. Changing how nulls compare across a whole composite key is not identical to limiting uniqueness to active rows.
Ryan Lambert’s July 2022 practitioner walkthrough demonstrates the PostgreSQL 15 feature during its beta period. It is useful historical explanation, not current AI-product evidence or a substitute for the deployed engine’s documentation.
Record the engine, version, schema and exact statements used in your own review. Do not assume that a lightweight test database reproduces production constraint behavior merely because both accept SQL.
Make existing data and deployment a separate decision
Before adding the chosen rule to an existing table, inspect active duplicates under the intended identity. A failed index build is useful evidence of a conflict to resolve. Do not have the assistant delete whichever record is easiest merely to make the migration pass.
The owner must decide which assignment is valid, what history to retain and how affected consumers should behave. Index creation, locking, old writers and rollout compatibility need their own rehearsal. Our migration guide addresses that transition; the fixture here does not establish a safe deployment plan.
For the next AI-assisted schema review, request the business-state table alongside the SQL. Run the forbidden insert, the reopening update and the legitimate history case on the target engine in an approved test environment. Preserve the actual errors and resulting rows. Accept the constraint only when it protects the intended states without silently prohibiting legitimate ones.