Database constraints

A database constraint is a rule declared in the schema (NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, or PostgreSQL’s EXCLUDE) that the database checks on every write and refuses to break. Because the check runs inside the engine, it binds every program that writes to the table and holds when transactions run at once. That makes constraints the last line of defence for your invariants and one of the cheapest race-free concurrency tools you have.

Picture it first: the temple shoe stand

Outside a busy temple, three volunteers run a shoe stand with 300 numbered cubbies and 300 brass tokens.

In the first version, they share a notebook of free cubbies. Each glances at it, picks a free number, puts the shoes in, then ticks it off. Two volunteers glance at the same moment, both see 42 free, and both use cubby 42. One family walks home in the wrong chappals.

In the second version, each token hangs on its own cubby’s hook, and using cubby 42 means taking token 42. Two volunteers can still reach for it together, but only one hand gets the token; the other finds an empty hook and moves on. Nobody has to be careful, because the stand makes the mistake impossible.

Rules painted on the wall (every pair gets a token, no bags) bind the regulars, the new volunteer who never saw the notebook, and the priest’s nephew helping for an hour.

Map it back:

• The notebook is application validation: SELECT, then INSERT. Correct alone, broken under concurrency.
• The token on its hook is a unique index: check and claim are one atomic step.
• The painted rules are NOT NULL and CHECK.
• The new volunteer is the backfill script or second service that never read your validation code.

Why the database has to say no

Your validation runs in one code path, but your data is written by many: the API, an admin panel, a support engineer’s UPDATE, a nightly import, a hurried backfill, a second service. A constraint is checked by the engine for all of them, at every isolation level, and no code path can forget it.

The second reason is concurrency. Here is a signup handler, run twice at once because Asha tapped “Create account” on her phone and her laptop:

SELECT 1 FROM users WHERE email = 'asha@example.com';   -- no row, so continue
INSERT INTO users (email, full_name) VALUES ('asha@example.com', 'Asha Rao');

At READ COMMITTED a SELECT sees only committed rows, so if both checks run before either insert commits, both see nothing and both insert. REPEATABLE READ fails too, since each snapshot predates the other’s insert (the write phantom of phantom read). Only SERIALIZABLE catches it, by aborting one transaction for you to retry.

Now add the rule to the schema:

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

The second INSERT now finds an index entry with the same key, written by a transaction that has not committed. PostgreSQL’s rule is that the would-be inserter waits to see whether that transaction commits. If T1 commits, T2 fails with SQLSTATE 23505 (unique_violation); if T1 rolls back, T2’s insert goes through. InnoDB looks the same from outside: T2 queues for a shared lock on the duplicate index record, then gets error 1062 ER_DUP_ENTRY (SQLSTATE 23000).

Keep your API validation anyway. It gives friendly messages without a round trip and checks what a database cannot, such as whether an email is deliverable. Treat it as user experience and the constraint as the guarantee: as ACID transactions puts it, the database enforces the invariants you declare, and everything else is your code’s job.

The toolbox, one constraint at a time

The running example is a homestay booking platform: guests, hosts, rooms and bookings.

NOT NULL, and DEFAULT beside it

NOT NULL says a value must be present. PostgreSQL’s docs suggest most columns should carry it: every nullable column forces readers into three-valued logic, and every “unknown” you allow is a branch someone forgets.

DEFAULT is not a constraint. It fills a value only when an INSERT omits the column; an explicit NULL still goes in unless the column is also NOT NULL. Pair them: status text NOT NULL DEFAULT 'confirmed'.

CHECK: rules about one row

A CHECK is a Boolean expression every inserted or updated row must satisfy:

CREATE TABLE bookings (
  booking_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id      bigint      NOT NULL,
  guest_id     bigint      NOT NULL,
  check_in     date        NOT NULL,
  check_out    date        NOT NULL,
  guests       smallint    NOT NULL DEFAULT 1,
  status       text        NOT NULL DEFAULT 'confirmed',
  amount_paise bigint      NOT NULL,
  refund_paise bigint,
  CONSTRAINT stay_has_nights     CHECK (check_out > check_in),
  CONSTRAINT guests_in_range     CHECK (guests BETWEEN 1 AND 6),
  CONSTRAINT valid_status        CHECK (status IN ('held', 'confirmed', 'cancelled')),
  CONSTRAINT amount_not_negative CHECK (amount_paise >= 0),
  CONSTRAINT refund_within_paid  CHECK (refund_paise <= amount_paise)
);

Three rules govern it:

• It is row-local. PostgreSQL does not support a CHECK that reads other rows or tables. No subqueries are allowed, and a function that peeks elsewhere may seem to work but can leave violating rows and break dump and restore.
• It must be immutable. It is checked only when a row is written, so CHECK (check_in >= current_date) is a bug: true on Monday, false for the same row on Friday.
• Name it. The name appears in the error, which is how you map a violation to a message.

MySQL enforces CHECK from 8.0.16; earlier versions parsed and silently ignored it, so a schema that grew up on 5.7 may hold rows breaking rules everyone assumed existed. A violation is error 3819 with SQLSTATE HY000, not a class-23 code.

UNIQUE and PRIMARY KEY

UNIQUE says no two rows share the listed columns’ values, enforced by a unique B-tree index (database indexes). PRIMARY KEY is UNIQUE plus NOT NULL, one per table, and the default target of foreign keys; the usual design is a surrogate primary key plus UNIQUE on the natural key (data modeling). Two traps:

• NULLs are distinct by default. Many rows may have a NULL gstin, and in UNIQUE (host_id, gstin) a row with a NULL part never collides; MySQL behaves the same. PostgreSQL 15 added NULLS NOT DISTINCT for “at most one NULL”.
• Case is a collation question. In PostgreSQL, UNIQUE (email) lets Asha@x.in and asha@x.in coexist, so index lower(email). MySQL’s default collation, utf8mb4_0900_ai_ci, is case- and accent-insensitive, so a plain unique index already rejects the variant (and treats rené and rene as equal).

Partial and expression unique indexes

Some rules apply to a subset of rows. “A host has at most one active payout account” cannot be a plain constraint, because retired accounts must not collide. A partial unique index indexes only rows matching its WHERE, so it enforces uniqueness only among them:

CREATE UNIQUE INDEX one_active_payout
  ON payout_accounts (host_id)
  WHERE status = 'active';

CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));   -- expression index

Partial indexes have edges. ON CONFLICT must repeat the predicate to pick the index (ON CONFLICT (host_id) WHERE status = 'active'). PostgreSQL will not turn a partial or expression index into a named constraint, and a foreign key cannot reference one. MySQL has no partial indexes; the usual emulation is a unique stored generated column that is NULL outside the subset, relying on NULLs being distinct:

ALTER TABLE payout_accounts
  ADD COLUMN active_host_id BIGINT AS (IF(status = 'active', host_id, NULL)) STORED,
  ADD UNIQUE KEY one_active_payout (active_host_id);

FOREIGN KEY, briefly

A foreign key says a value in the child row must exist as a key in the parent table (or be NULL), and decides what happens when the parent is deleted. It carries the most operational baggage: PostgreSQL does not index the referencing columns, its checks lock parent rows, and big MySQL shops often drop foreign keys. All of that is in foreign key design.

EXCLUDE: no two rows may overlap

UNIQUE asks “are these two rows equal?” An exclusion constraint lets you choose the comparison. It guarantees that for any two rows, at least one of the listed operator comparisons is false (or NULL). With = everywhere it is a slower UNIQUE; with && (overlaps) it becomes “no two bookings of the same room share a night”:

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- GiST support for = on bigint

ALTER TABLE bookings
  ADD CONSTRAINT no_double_booked_nights
  EXCLUDE USING gist (
    room_id                        WITH =,
    daterange(check_in, check_out) WITH &&
  ) WHERE (status <> 'cancelled');

Read it as: two non-cancelled bookings conflict if they share a room_id and their stays overlap.

• Half-open ranges. daterange(check_in, check_out) uses the canonical [) form, so a 24 to 27 December stay covers the nights of the 24th, 25th and 26th. Checking out on the 27th and checking in that day do not overlap, so the room turns over the same day.
• GiST, plus btree_gist. B-trees cannot answer “overlaps”, so exclusion constraints use GiST (or SP-GiST); the trusted btree_gist extension adds GiST support for = on plain bigint.
• The WHERE clause makes it a partial constraint, so cancelled bookings never block a room.
• Concurrency works like UNIQUE. A conflicting insert waits for the other transaction, then fails with 23P01 (exclusion_violation) if it committed: 26 to 28 December collides with 24 to 27 on the night of the 26th.

The limits: EXCLUDE is PostgreSQL-only, ON CONFLICT DO NOTHING can use it but DO UPDATE cannot, and it compares rows in pairs, so it can say “no two overlap” but not “at most 6 overlap” (a six-bed dormitory). PostgreSQL 18 adds a shorthand: with a range column stay, PRIMARY KEY (room_id, stay WITHOUT OVERLAPS) acts like this constraint minus the WHERE, and foreign keys can reference it with PERIOD. Phantom read shows the concurrency side.

Domains, enums and lookup tables

A domain is a type with constraints attached, defined once and reused across tables:

CREATE DOMAIN pin_code AS text CHECK (VALUE ~ '^[1-9][0-9]{5}$');
CREATE DOMAIN paise    AS bigint CHECK (VALUE >= 0);

CREATE TABLE properties (
  property_id bigint PRIMARY KEY,
  pin         pin_code NOT NULL,
  deposit     paise    NOT NULL DEFAULT 0
);

PostgreSQL’s docs advise putting NOT NULL on columns rather than the domain, because SQL can produce a NULL of the domain type (in outer joins, say) where the domain check never runs. MySQL has no domains.

For a fixed set of values such as booking status you have three choices:

• CHECK (status IN (…)) is simple and portable; adding a value means dropping and re-adding the constraint.
• An enum type is compact (4 bytes on disk in PostgreSQL). You can add and rename values, but not remove or reorder them without recreating the type. MySQL’s ENUM rejects unknown values only in strict SQL mode.
• A lookup table plus a foreign key turns values into data: adding one is an INSERT, and each can carry columns such as display_name. You pay a foreign-key check per write and a join to show the label.

Pick a lookup table when values change often or carry attributes, an enum or CHECK when they almost never change.

The constraint types side by side

Deferrable constraints

By default PostgreSQL checks UNIQUE and PRIMARY KEY row by row, as each row is written; the SQL standard says end of statement. The difference shows when one statement moves several keys. Room photos have UNIQUE (room_id, position), and the host adds a new cover photo:

UPDATE room_photos SET position = position + 1 WHERE room_id = 12;

If position 1 is updated first, it becomes 2 while the old 2 still exists, so success depends on physical row order. DEFERRABLE INITIALLY IMMEDIATE switches to the standard’s end-of-statement check (PostgreSQL warns it can be significantly slower). INITIALLY DEFERRED, or SET CONSTRAINTS … DEFERRED, postpones the check to COMMIT:

ALTER TABLE room_photos
  ADD CONSTRAINT photo_position_key UNIQUE (room_id, position)
  DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS photo_position_key DEFERRED;
UPDATE room_photos SET position = 2 WHERE photo_id = 501;   -- was 1
UPDATE room_photos SET position = 1 WHERE photo_id = 502;   -- was 2
COMMIT;                                                     -- checked here

The other classic need is a circular reference: a host row pointing at its default payout account while each account points back at its host. Neither row can go in first unless one foreign key waits until commit (foreign key design).

In PostgreSQL only UNIQUE, PRIMARY KEY, EXCLUDE and FOREIGN KEY can be deferred; NOT NULL and CHECK are always checked as each row is written. Deferral has costs: a deferrable unique or exclusion constraint cannot be an ON CONFLICT arbiter or a foreign-key target, and the error surfaces at COMMIT, far from its cause.

MySQL has no deferrable constraints; InnoDB checks unique and foreign-key constraints row by row and immediately. The manual’s fix for the shift above is a safe order, UPDATE … SET position = position + 1 ORDER BY position DESC; for a swap, park one row at a free value such as -1 first.

When a constraint says no

A violation is not a crash; it is the database telling you a business rule fired. Map each one to a response:

Branch on the SQLSTATE (or the MySQL error number, since all MySQL integrity errors share 23000) plus the constraint name, which PostgreSQL includes in the error; mapping users_email_lower_key to a message beats parsing text. Don’t blindly retry class-23 errors: unlike a deadlock, the same statement gets the same answer.

Better still, let the constraint drive the statement. PostgreSQL’s INSERT … ON CONFLICT takes an alternative action instead of raising the error, and DO UPDATE guarantees an atomic insert-or-update even under high concurrency:

INSERT INTO users (email, full_name)
VALUES ('asha@example.com', 'Asha Rao')
ON CONFLICT ((lower(email))) DO NOTHING        -- infers the expression index
RETURNING user_id;                             -- no row back: already registered

INSERT INTO guest_preferences (guest_id, language)
VALUES (77, 'hi')
ON CONFLICT (guest_id) DO UPDATE SET language = EXCLUDED.language;

MySQL’s equivalent is INSERT … ON DUPLICATE KEY UPDATE (1 affected row for an insert, 2 for an update). The manual advises against it on tables with several unique indexes, where a row can match two of them.

Idempotency keys are a UNIQUE constraint

A client that retries “book this room” after a timeout must not create two bookings. Give each request a key and let a primary key decide which attempt is first (idempotency):

CREATE TABLE booking_requests (
  guest_id        bigint      NOT NULL,
  idempotency_key uuid        NOT NULL,
  booking_id      bigint,
  created_at      timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (guest_id, idempotency_key)    -- scoped per guest
);

INSERT INTO booking_requests (guest_id, idempotency_key)
VALUES ($1, $2)
ON CONFLICT DO NOTHING
RETURNING created_at;
-- 1 row: first attempt, so create the booking in this same transaction.
-- 0 rows: a retry; read booking_id and return the stored result.

A retry that arrives mid-flight waits on the unique check; if the first attempt commits, the retry gets 0 rows, and if it rolls back, the retry does the work.

Adding constraints to a live table

Suppose bookings has 500M rows of about 200 bytes: 500M × 200 B = 100 GB. A plain ALTER TABLE … ADD CONSTRAINT … CHECK takes an ACCESS EXCLUSIVE lock, which blocks even SELECT, and scans the table while holding it. At 500 MB/s that is 100,000 MB ÷ 500 MB/s = 200 seconds; at 2,000 writes a second, 2,000 × 200 = 400,000 writes queue or time out.

PostgreSQL splits the job in two:

SET lock_timeout = '2s';   -- give up rather than stall traffic behind the lock

ALTER TABLE bookings
  ADD CONSTRAINT amount_not_negative CHECK (amount_paise >= 0) NOT VALID;  -- no scan
ALTER TABLE bookings VALIDATE CONSTRAINT amount_not_negative;              -- scan

NOT VALID skips the scan, so the strong lock lasts milliseconds, and every new or updated row is checked from then on. VALIDATE CONSTRAINT scans old rows under SHARE UPDATE EXCLUSIVE, which does not conflict with ordinary reads and writes; that is safe because other transactions already enforce the rule on what they write. Old violators can be fixed before validating while no new ones get in. NOT VALID works for CHECK, FOREIGN KEY and, from PostgreSQL 18, NOT NULL.

Recipes for the other kinds:

• NOT NULL before PostgreSQL 18. SET NOT NULL normally scans under ACCESS EXCLUSIVE, but since PostgreSQL 12 it skips the scan when a valid CHECK proves no NULLs exist. Backfill in batches, add CHECK (guest_phone IS NOT NULL) NOT VALID, validate it, SET NOT NULL (now instant), then drop the CHECK. On 18, add NOT NULL guest_phone NOT VALID directly and validate it.

• UNIQUE. Build the index without blocking writes, then attach it:

  CREATE UNIQUE INDEX CONCURRENTLY users_phone_key ON users (phone);
  ALTER TABLE users ADD CONSTRAINT users_phone_key UNIQUE USING INDEX users_phone_key;

  The concurrent build scans twice and cannot run in a transaction block. If it fails (on a duplicate, say), it leaves an INVALID index that still costs writes and may still enforce uniqueness: drop it, clean up, retry. USING INDEX rejects partial and expression indexes, which stay plain unique indexes.

• EXCLUDE has neither NOT VALID nor USING INDEX, so its GiST index builds under the strong lock. Add it while the table is small, or migrate into a new table that has it.

In MySQL 8.4, online DDL builds secondary indexes (unique ones included) while writes continue, and MODIFY … NOT NULL rebuilds the table in place without blocking DML. A foreign key is added in place only with foreign_key_checks off, leaving existing rows unchecked; otherwise the table is copied. Online schema and data migrations has the wider playbook.

What constraints cost

• Index maintenance. Every UNIQUE, PRIMARY KEY and EXCLUDE is an index updated on every insert. At 5,000 bookings a second with a primary key, two unique constraints and an exclusion constraint, that is 5,000 × 4 = 20,000 index insertions a second, plus log records (database indexes has the arithmetic).
• Per-row checks. NOT NULL and CHECK are CPU work on a row already in hand; PostgreSQL implements NOT NULL more efficiently than the equivalent CHECK.
• Lock waits. A duplicate insert waits for the other transaction’s whole life, so keep such transactions short. In InnoDB the waiter takes a shared lock on the duplicate record, and the manual shows three sessions inserting one key deadlocking when the first rolls back.
• Change. Deferrable unique checks are slower, and editing a CHECK IN (…) list is a migration.

The alternative costs more: app-level duplicate detection needs the same index to search, plus cleanup for duplicates that slip through.

Rules no constraint can express

Every constraint has a scope, and the scope decides which rules it can hold:

Take a property with 20 identical Deluxe rooms: at most 20 Deluxe bookings per night. That is a count over many rows, and no constraint counts. An app that runs SELECT count(*), sees 19 and inserts will oversell when two guests do it at once. Three workarounds:

Materialize the count into one row and constrain that. Keep one inventory row per room type per night, and make overselling a CHECK violation:

CREATE TABLE room_type_nights (
  room_type_id bigint NOT NULL,
  night        date   NOT NULL,
  capacity     int    NOT NULL,
  sold         int    NOT NULL DEFAULT 0,
  PRIMARY KEY (room_type_id, night),
  CONSTRAINT not_oversold CHECK (sold BETWEEN 0 AND capacity)
);

-- inside the booking transaction: one row per night of the stay
UPDATE room_type_nights
   SET sold = sold + 1
 WHERE room_type_id = 3
   AND night >= '2026-12-24' AND night < '2026-12-27';
-- 23514 not_oversold: some night is full, so roll back

Bookings for one night queue on its row lock. At READ COMMITTED the 21st re-reads the committed sold, computes 21 and fails the CHECK; at REPEATABLE READ it gets a serialization error to retry. You gain a race-free rule on any SQL database. You pay with a hot row, since every booking for a popular night serializes on it (contention and double booking), and overlapping multi-night stays must lock nights in date order to avoid deadlocks.

A trigger plus a lock. A counting BEFORE INSERT trigger binds every writer, but races like app code unless it first locks something shared, such as the room-type row. You gain enforcement in the database; you pay with hidden logic and a serialization point.

SERIALIZABLE and a retry loop. At SERIALIZABLE the engine aborts one of two conflicting count-then-inserts with 40001. You gain any predicate with no extra schema; you pay in retries and false aborts, and every writer must use that level (serializable isolation).

Small fixed limits have a fourth trick, numbered slots: “at most 3 sessions per user” becomes CHECK (slot BETWEEN 1 AND 3) plus UNIQUE (user_id, slot), as in phantom read.

Going deeper: constraints across shards and services

A unique index lives on one node. Once users are sharded by user_id, each shard’s index sees only its own rows, and two signups with one email on different shards both succeed. Every fix makes one place own the unique value:

• Shard by the unique value. The local index then suffices, but everything else usually wants user_id routing.
• A global uniqueness table. email_claims(email_lower PRIMARY KEY, user_id), sharded by a hash of the email, is the one place an email can live. Claim first (INSERT … ON CONFLICT DO NOTHING), then create the user on its shard. The two writes are not atomic, so order them for the safe failure: an orphan claim blocks an email until a reconciler frees it, while a user without a claim would allow a duplicate. Vitess’s Lookup Vindex has this shape: a MySQL lookup table, possibly on another shard, mapping a column value to the shard holding the row.
• Conditional writes in DynamoDB. A condition is evaluated against the one item named by the request’s key, so attribute_not_exists(pk) makes a put fail if that item exists. For uniqueness on another attribute, write a marker item such as EMAIL#asha@example.com beside the user item, both conditional, in one all-or-nothing TransactWriteItems.
• Distributed SQL. CockroachDB and Spanner enforce UNIQUE cluster-wide, since their indexes are distributed like tables; the check may be a remote read, so you pay in write latency rather than code.

Across microservices there is no constraint at all: a rule spanning services becomes one owner’s constraint plus idempotent events and reconciliation (foreign key design, sharding a relational database).

Cheat sheet

How to say it in the interview

“I put every invariant I can into the schema, because constraints bind every writer and they’re race-free. Signup gets a unique index on lower(email): if two requests race, the second waits and fails with 23505, which I map to a 409. Money gets CHECK (amount >= 0), bookings an exclusion constraint so stays can’t overlap, and ‘at most 20 rooms a night’ a counter row with CHECK (sold <= capacity). The API still validates, but that’s UX. On big live tables I add constraints NOT VALID and validate separately, so the scan never blocks writes.”

App validation or a database constraint? Both, with different jobs. App validation gives fast, friendly feedback and checks things the database can’t. The constraint is the guarantee: it covers scripts, admin tools and other services, and closes the gap between SELECT and INSERT that concurrent requests slip through.

How do you add NOT NULL to a 500M-row table? Make writers fill the column, backfill in batches, add CHECK (col IS NOT NULL) NOT VALID and VALIDATE it under a lock that doesn’t block writes. SET NOT NULL then skips its scan, and you drop the check. On PostgreSQL 18, add the NOT NULL itself as NOT VALID. Set lock_timeout throughout.

How do you enforce a unique email across shards? Give the value one owner: a claims table keyed and sharded by email, written before the user row so a failure leaves only a harmless orphan claim, or a conditional DynamoDB marker item. Distributed SQL enforces it natively, at a latency cost.

Why not SELECT … FOR UPDATE before the insert? On PostgreSQL a locking read that finds no rows locks nothing, so both requests still pass. InnoDB’s gap locks can block the second insert at REPEATABLE READ, but they invite deadlocks and vanish at READ COMMITTED. The unique index is simpler and always works.

Where to go next

• Foreign key design: actions, indexing, locking, and when large systems drop foreign keys.
• Database normalization: how keys and unique constraints fall out of functional dependencies.
• Phantom read: the check-then-insert race in depth, and how each engine handles it.
• Serializable isolation: the general fix for rules no constraint can express.
• Database indexes: the B-trees behind UNIQUE and PRIMARY KEY, and their write cost.
• Idempotency: request keys enforced by a unique constraint.
• Online schema and data migrations: backfills and DDL without downtime.
• Contention and double booking: hot counter rows and inventory under load.

References and further reading

1. PostgreSQL documentation: Constraints (section 5.5)postgresql.orgCHECK passes on NULL and must not look at other rows; NULLs are distinct in UNIQUE unless NULLS NOT DISTINCT; primary keys imply NOT NULL; exclusion constraints create an index.
2. PostgreSQL documentation: CREATE TABLEpostgresql.orgWhich constraint types are deferrable, EXCLUDE semantics and index methods, WITHOUT OVERLAPS, NOT ENFORCED, and the note that non-deferrable uniqueness is checked row by row.
3. PostgreSQL documentation: ALTER TABLEpostgresql.orgNOT VALID and VALIDATE CONSTRAINT with their lock levels, ADD CONSTRAINT … USING INDEX and its limits, and SET NOT NULL skipping the scan when a valid CHECK proves it.
4. PostgreSQL documentation: Index uniqueness checks (section 63.5)postgresql.orgAn inserter that meets an uncommitted duplicate waits for that transaction, then reports a violation only if it committed.
5. PostgreSQL documentation: INSERT, the ON CONFLICT clausepostgresql.orgArbiter inference with expressions and partial-index predicates, atomic DO UPDATE, and why deferrable and exclusion constraints cannot arbitrate DO UPDATE.
6. PostgreSQL documentation: SET CONSTRAINTSpostgresql.orgNOT NULL and CHECK are always checked immediately; only UNIQUE, PRIMARY KEY, FOREIGN KEY and EXCLUDE can be deferred to commit.
7. PostgreSQL documentation: Appendix A, error codespostgresql.orgClass 23 SQLSTATEs: 23502, 23503, 23505, 23514 and 23P01.
8. MySQL 8.0 Reference Manual: CHECK constraintsdev.mysql.comCHECK was parsed and ignored before 8.0.16 and enforced from 8.0.16; expressions must be deterministic and cannot use subqueries.
9. MySQL 8.4 Reference Manual: Locks set by different SQL statements in InnoDBdev.mysql.comA duplicate-key error takes a shared lock on the duplicate record, the three-session deadlock that follows, and ON DUPLICATE KEY UPDATE taking exclusive locks.
10. Amazon DynamoDB Developer Guide: Condition expressionsdocs.aws.amazon.comattribute_not_exists on the key makes a put fail if the item exists, evaluated against the single item named by the request’s key.