Constraints: Where Business Rules Belong

Should a rule like "one patient per phone number" live in the app, where it can show a friendly message, or in the database, where it cannot be bypassed? Both, and this part shows why the second is not optional. Every constraint gets one insert that passes and one that fails, on both engines, and then two sessions try to book the same slot at the same moment, once with the rule in the app and once with it in the database.

Every rule with a name

Section 1 builds the two tables with every rule named, in PostgreSQL:

CREATE TABLE patients (
    id          bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_patients PRIMARY KEY,
    full_name   varchar(200) NOT NULL,
    phone       varchar(30)  NOT NULL CONSTRAINT uq_patients_phone UNIQUE,
    email       varchar(200) NULL,
    created_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE TABLE appointments (
    id                 bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_appointments PRIMARY KEY,
    patient_id         bigint NOT NULL CONSTRAINT fk_appointments_patients REFERENCES patients (id),
    starts_at          timestamptz NOT NULL,
    status             varchar(20) NOT NULL DEFAULT 'Booked',
    confirmation_code  varchar(6)  NOT NULL CONSTRAINT uq_appointments_confirmation_code UNIQUE,
    created_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_appointments_status
        CHECK (status IN ('Booked', 'CheckedIn', 'InProgress', 'Done', 'Cancelled', 'NoShow')),
    CONSTRAINT ck_appointments_starts_after_created CHECK (starts_at > created_at),
    CONSTRAINT ck_appointments_code_length CHECK (char_length(confirmation_code) = 6)
);

The prefixes are the convention Part 8 spells out: pk_, uq_, ck_, fk_, and on SQL Server df_, because there a default is a constraint with a name too. Section 4e shows what you get without one: the capacity check on rooms fails as CK__rooms__capacity__44FF419A on SQL Server, with a suffix that differs in every database, and as rooms_capacity_check on PostgreSQL. Section 5b shows the SQL Server cost of an unnamed default: DROP COLUMN is_open is refused because DF__rooms__is_open__45F365D3 depends on the column, and you cannot drop by a name you have to look up first. PostgreSQL treats a default as a column property and the column drops with it.

Pass once, fail once

Sections 2 to 5 are a catalogue of refusals, and reading each once teaches you to read them all. NOT NULL, for a patient without a name:

ERROR:  null value in column "full_name" of relation "patients" violates not-null constraint
DETAIL:  Failing row contains (2, null, +00-20-5550-0199, null, 2026-09-25 05:18:53.482698+00).

SQL Server's is Msg 515, Cannot insert the value NULL into column 'full_name', table 'dd_p06.dbo.patients'; column does not allow nulls. Neither engine gives a NOT NULL rule a usable name in the message, so this is the one rule the app should check first, to say which field is missing. UNIQUE, for Aisha Khan registering with Maria's phone:

ERROR:  duplicate key value violates unique constraint "uq_patients_phone"
DETAIL:  Key (phone)=(+00-20-5550-0101) already exists.

CHECK, three times, for a status the clinic does not have, a booking dated yesterday, and a four-character code; each error names its constraint, and PostgreSQL prints the whole failing row in the DETAIL line. DEFAULT fills what the insert leaves out, so David Chen's appointment without a status became Booked with a timestamp; but an explicit NULL is not "left out", the default does not apply, and NOT NULL fires. A run of failed inserts also leaves the identity gaps Part 1 mentioned: David's row got id 5.

Unique, and NULL

Email is optional but should be unique when given. Maria and David have none. Section 3b adds the constraint with two NULL emails present, and the engines disagree. PostgreSQL treats NULLs as distinct, so UNIQUE (email) succeeds; SQL Server counts one NULL as a value, so the same constraint fails with Msg 1505, The duplicate key value is (<NULL>). Each engine can imitate the other: SQL Server with a filtered unique index, WHERE email IS NOT NULL, and PostgreSQL 15 and later with UNIQUE NULLS NOT DISTINCT, which the script runs to show it failing exactly as SQL Server does. Once the rule is in place, Tomás Silva with Aisha's email is refused on both.

The rule that needs a filter

ClinicLive's central rule is one active appointment per slot, and it is a unique index with a filter, so cancelled and no-show rows do not hold the slot:

CREATE UNIQUE INDEX ix_appointments_slot_active_unique ON appointments (starts_at)
    WHERE status NOT IN ('Cancelled', 'NoShow');

That is the app's index word for word. On SQL Server a filtered index does not accept NOT IN, so the same rule is spelled as the list of the four active statuses, which means a new active status is a change to the index as well. Section 6 then plays out the rule: Aisha Khan books tomorrow at 11:00, Tomás Silva is refused the same slot, Aisha cancels, Tomás is accepted, and Aisha cannot un-cancel back into it, because the index applies to UPDATE as much as INSERT:

       starts_at        |  full_name  |  status   | confirmation_code
------------------------+-------------+-----------+-------------------
 2026-09-26 11:00:00+00 | Aisha Khan  | Cancelled | M2K7Q4
 2026-09-26 11:00:00+00 | Tomás Silva | Booked    | T8R3J6

What the app should still do

A constraint error is not a message for a receptionist. The app's job is to translate it, and the constraint name is the key. PostgreSQL returns the name as a field of the error (in .NET, PostgresException.ConstraintName); section 7a does it in SQL:

DO $$
DECLARE
    broken_rule text;
BEGIN
    INSERT INTO patients (full_name, phone) VALUES ('Liam O''Brien', '+00-20-5550-0101');
EXCEPTION WHEN unique_violation THEN
    GET STACKED DIAGNOSTICS broken_rule = CONSTRAINT_NAME;
    RAISE NOTICE 'constraint_name = %, message for the receptionist: %', broken_rule,
        CASE broken_rule
            WHEN 'uq_patients_phone' THEN 'This phone number already belongs to a patient.'
            WHEN 'ix_appointments_slot_active_unique' THEN 'That time was just taken. Please pick another.'
            ELSE 'Something went wrong.'
        END;
END $$;

SQL Server exposes the name only inside the text of ERROR_MESSAGE(), so the SQL Server script's TRY/CATCH searches for it. Either way the mapping is a small table the app owns, and it is the reason section 1 named everything. The app should also validate before it submits, for the message it can give, for the field it can point at, and for the round trip it saves. What it cannot do is guarantee the rule, and the race shows why.

Two sessions, one slot

Session A and session B each do what a booking page does: check that tomorrow 15:00 is free, wait three seconds while the user presses Confirm, insert, commit. B starts one second after A. The first round runs against a copy of the table with no unique index, an app that trusts its own check. Both checks see zero bookings, both inserts succeed, and the slot is booked twice:

 appointments_unguarded | 2026-09-26 15:00:00+00 | Priya Nair      | RACEA1
 appointments_unguarded | 2026-09-26 15:00:00+00 | Fatima Al-Sayed | RACEB1

The second round runs against the real table, with A holding its transaction open for three seconds after inserting. B's check still sees zero, because A has not committed. Then B's insert waits:

 session |   step    |   utc_time
---------+-----------+--------------
 B       | inserting | 05:19:05.210

ERROR:  duplicate key value violates unique constraint "ix_appointments_slot_active_unique"
DETAIL:  Key (starts_at)=(2026-09-26 15:00:00+00) already exists.
COMMIT;
ROLLBACK

 session |      step       |   utc_time
---------+-----------------+--------------
 B       | after the error | 05:19:07.219

A committed at 05:19:07.218 and B's insert failed one millisecond later, after waiting two seconds on A's uncommitted key. SQL Server behaved the same way, to the millisecond: B blocked for 1.989 seconds and then got Msg 2601, Cannot insert duplicate key row in object 'dbo.appointments' with unique index 'ix_appointments_slot_active_unique'. The difference is what happens next: PostgreSQL aborts B's whole transaction and its COMMIT prints ROLLBACK; SQL Server leaves the transaction open with the failed statement rolled back. Either way the real table holds one booking for 15:00, Priya Nair's, and the app's check never had a chance, because it ran before anything existed to see. No amount of code before the insert closes that gap; the unique index is what closes it.

Frequently asked

Should validation be in the database or in the application?

Both. The application validates for people, with field-level messages, before it submits. The database enforces the rules with named constraints, and it is the only place a rule about two rows or two users, such as one booking per slot, can actually be guaranteed.

Why does a unique constraint on a nullable column behave differently in SQL Server and PostgreSQL?

PostgreSQL treats NULLs as distinct, so many rows may have no value. SQL Server counts one NULL as a value, so a second NULL is a duplicate. Use a filtered unique index WHERE column IS NOT NULL in SQL Server, or UNIQUE NULLS NOT DISTINCT in PostgreSQL 15 and later to get the other behaviour.

How do I prevent double booking in a database?

With a unique index on the slot, filtered to active statuses so cancellations free the slot. Checking in the application before inserting is not enough: two sessions can both see the slot as free and both insert. With the index, the second insert waits for the first to commit and then fails.

Next: Part 7, normalisation, starting from a spreadsheet that has all of these rules broken at once. The error a seed script causes against a unique key is decoded in this Fixes post.