SQL primary keys and unique constraints

What it means

A constraint is a declared rule for acceptable table states. A primary key designates the row identifier; UNIQUE can impose a separate uniqueness rule without making that column the primary key. A label does not repair data that contradicts the rule.

Keep two questions separate: does the existing table state satisfy the declared constraint, and would the proposed value violate it? The scene evaluates the first question before it can give a meaningful answer to the second.

Try it

Simulation: begin with Constraint = UNIQUE, New value = NULL and Existing value = NULL. Change Existing value to alpha, then change New value to alpha. Inspect the result after each change, then select Compare with baseline to compare the final state with the original example. Reset restores the original inputs and clears errors and the comparison. No database is accessed. Without JavaScript, the initial example remains readable, but the controls do not operate.

The baseline result is “Accepted by this simplified uniqueness rule.” Default PostgreSQL UNIQUE treats NULL values as distinct for this check. After both values become alpha, the result is “Rejected as duplicate” because the proposed non-NULL key equals the stored key. Neither result evaluates other constraints, permissions or concurrent transactions.

Keep Constraint = UNIQUE throughout this comparison. Only Existing value and New value change; the PostgreSQL 18 default NULL policy and the model's single-column scope stay fixed.

Reset restores Constraint = UNIQUE, New value = NULL and Existing value = NULL, clears input errors and removes the comparison. It preserves your independent choices to open or close FAQ answers and About this model.

How it works

For the UNIQUE branch, the important intermediate question is whether the proposed value forms a conflicting key with the existing value. Under PostgreSQL 18's default NULL treatment, two NULLs are treated as distinct for this uniqueness check. A repeated non-NULL alpha does form a duplicate, so the model rejects it. This is a rule for the constraint, not a claim that NULL behaves like an ordinary value in every SQL comparison.

A primary key adds a non-NULL requirement. With Existing value = alpha and New value = NULL, changing only Constraint from UNIQUE to PRIMARY KEY changes acceptance to “Rejected.” No duplicate alpha was introduced; the proposed NULL now violates the additional requirement.

The model checks the starting state first. If Constraint = PRIMARY KEY and Existing value = NULL, it reports invalid input before evaluating the proposal—even when New value is beta. No insertion outcome applies until that contradiction is corrected.

Worked example

This is a synthetic baseline, not a production observation.

The result is “Accepted by this simplified uniqueness rule.” Although both inputs display NULL, PostgreSQL 18's default UNIQUE policy treats them as distinct for this check, so the proposed value does not create a conflicting key. This establishes only that the modeled uniqueness rule permits the pair—not that a real insertion would succeed.

What changes when the condition changes

Set New value = alpha and Existing value = alpha, keeping Constraint = UNIQUE. The result is “Rejected as duplicate”: the proposed non-NULL key equals the stored key. Unlike the baseline's two NULLs, these two alpha values form a duplicate under the modeled rule.

You can isolate the transition by changing Existing value to alpha first while New value remains NULL. That intermediate state is still accepted by this simplified rule. Changing only New value from NULL to alpha then creates the conflicting non-NULL pair.

In practice

For a database you are authorized to inspect, start with read-only evidence for the exact database, schema, table and key columns. Confirm the engine version, the constraint declaration, its NULL policy and the relevant existing data. A clean sample does not prove that every row satisfies the proposed rule.

For a valid single-column primary key, you should find neither NULL key values nor duplicate keys. If your evidence contradicts that expectation—or you cannot confirm which table or constraint you inspected—STOP before proposing a write or schema change. Resolve the mismatch rather than deleting rows or disabling enforcement to make the example fit.

This page supplies no database-changing procedure. Any actual change needs a separate plan for its exact scope, concurrent writes, locking impact, verification and reversal. Never treat “Accepted by this simplified uniqueness rule” as permission to write to a database.

Limits and unknowns

The scene covers one proposed value and one existing value under a single-column PostgreSQL 18 rule. It does not model composite keys, NULLS NOT DISTINCT, collations or actual database writes. An accepted result means only that the selected simplified key rule did not reject that input; other constraints, permissions and concurrent transactions are not evaluated.

If the real engine, declaration or existing data is unknown, the real database outcome remains unknown. The scene cannot retrieve that missing evidence. Consult PostgreSQL 18's constraint documentation, sections 5.5.3–5.5.4, and unique-index documentation, section 11.6, for the documented rules within this page's scope.

What to carry forward

Before predicting rejection, identify the key columns, the declared constraint and its NULL policy. A value can pass one uniqueness rule without being acceptable to the table as a whole. Your next useful observation is the actual table definition—not the model's accepted result.