Types, Constraints & Database Objects

Primary, Unique, and Check Constraints

Candidate keys identify rows, primary and unique constraints declare chosen keys, and CHECK constrains each row—but SQL nulls make uniqueness vendor-sensitive and a CHECK rejects FALSE while allowing UNKNOWN.

A candidate key is minimal and unique by business meaning.
A surrogate key does not replace natural integrity.
Null behavior in unique constraints is vendor-sensitive.
A CHECK succeeds on TRUE or UNKNOWN and fails only on FALSE.
CHECK is best for row-local predicates.
Constraint names and lifecycle matter operationally.

Declare every candidate-key rule

CREATE TABLE order_external_refs (
  source_system VARCHAR(40) NOT NULL,
  external_order_id VARCHAR(120) NOT NULL,
  order_id BIGINT NOT NULL REFERENCES orders(order_id),
  CONSTRAINT pk_order_external_refs
    PRIMARY KEY (source_system, external_order_id),
  CONSTRAINT uq_order_external_refs_order UNIQUE (source_system, order_id),
  CONSTRAINT ck_order_external_refs_source CHECK (source_system <> '')
);

ALTER TABLE order_items
  ADD CONSTRAINT uq_order_items_product UNIQUE (order_id, product_id);

CHECK three-valued evaluation
`quantity`	`quantity > 0`	CHECK result
`2`	TRUE	Accept
`0`	FALSE	Reject
`NULL`	UNKNOWN	Accept; NOT NULL must reject absence

Nullable uniqueness caveat
Engine / declaration	Two rows with NULL key?
PostgreSQL default UNIQUE	Allowed
PostgreSQL `UNIQUE NULLS NOT DISTINCT`	Rejected
MySQL UNIQUE	Allowed
SQLite UNIQUE	Allowed
SQL Server ordinary unique single-column index	Only one NULL; use a filtered index to ignore NULLs
Oracle single nullable unique column	Multiple all-null key rows because entirely null index entries are absent