Constraints

Choosing the rules

Begin with what a valid record means. If a purchase line counts whole items being bought, its quantity must be a positive integer. A missing quantity would leave the purchase incomplete, so presence is a separate requirement. These decisions tell us which rules to declare; choosing a column type is only the first part.

The rule also needs the right scope. A line number identifies one entry within an order. Many orders can have a line 1, so requiring line numbers to be unique throughout the table would reject ordinary purchases. The combination of order ID and line number must be unique. Similarly, a product may appear on several lines of one order, perhaps at different agreed prices. A uniqueness rule on the order and product would prevent that useful distinction.

Too few constraints allow contradictions that later code must detect and repair. The wrong constraints make legitimate work fail, encouraging callers to invent placeholder values or work around the design. A useful review therefore tries both kinds of example: a record the database should refuse and an ordinary operation it must still allow.

Some requirements concern one field, such as whether a name is present. Others concern a row, a whole table, or a reference to another table. The constraint types below correspond to these different jobs. None can decide whether our chosen rule accurately describes the shop.

The shop’s rules

The customer C4 is Ada. Order O12 belongs to C4. Its first line buys two of product P7, the Blue mug, at an agreed €18 each. P7’s current catalogue price is €20. These are the same facts as in Designing a relational schema.

For this design, every order has a customer and a placement time. Every line has a real order, a real product, a positive integer line number, and a positive whole-number quantity. Prices can be zero, because the shop may give an item away, but cannot be negative. We still assume one currency, EUR.

Products may also have an assigned catalogue code called a SKU. When one is assigned, no other product can use it. Products awaiting a code may leave it missing. Unlike the product ID, this code is optional. We need to prevent two products from sharing an assigned code while still allowing several products to await one.

We use NOT NULL for required values and CHECK for conditions such as a positive quantity. PRIMARY KEY and UNIQUE prevent duplicate identities or codes. A foreign key, written with REFERENCES, requires a value to match a record in another table. Together these declarations make the shop’s rules part of its schema.

In a CREATE TABLE definition, each column starts with its name and type, followed by any rules for that column. A declaration such as PRIMARY KEY (order_id, line_no) names a rule over several columns. The sections below explain each declaration. The complete table definitions are here if you want to run the examples; the deletion actions are choices for this shop, which we’ll examine after the value and identity rules.

CREATE TABLE customers (
  customer_id text PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE products (
  product_id text PRIMARY KEY,
  name text NOT NULL,
  current_price numeric(12,2) NOT NULL
    CHECK (current_price >= 0 AND current_price <> 'NaN'::numeric),
  sku text UNIQUE
);

CREATE TABLE orders (
  order_id text PRIMARY KEY,
  customer_id text NOT NULL
    REFERENCES customers (customer_id) ON DELETE RESTRICT,
  placed_at timestamptz NOT NULL
);

CREATE TABLE order_lines (
  order_id text NOT NULL
    REFERENCES orders (order_id) ON DELETE CASCADE,
  line_no integer NOT NULL CHECK (line_no > 0),
  product_id text NOT NULL
    REFERENCES products (product_id) ON DELETE RESTRICT,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL
    CHECK (unit_price >= 0 AND unit_price <> 'NaN'::numeric),
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO customers (customer_id, name) VALUES ('C4', 'Ada');
INSERT INTO products
  (product_id, name, current_price, sku)
VALUES ('P7', 'Blue mug', 20.00, 'MUG-BLUE'),
       ('P8', 'Bowl', 24.00, NULL);
INSERT INTO orders (order_id, customer_id, placed_at) VALUES
  ('O12', 'C4', '2026-10-04 09:00:00+00');
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES
  ('O12', 1, 'P7', 2, 18.00);

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

Alternatively, download the practice schema and records for an empty database. It includes the same O12 purchase and the additional order O13 used in the schema-design article. Run failed-write examples as separate statements outside an explicit transaction; after an error inside a transaction, PostgreSQL requires a rollback or recovery to a savepoint before ordinary work continues.

Required values and checks

Presence and an acceptable value are two different requirements. NOT NULL requires a value to be present. On quantity, it rejects a line whose quantity is missing. On name, it rejects a missing name, but still permits an empty string. “Present” and “non-empty” are different rules; a rule about acceptable names would need more than NOT NULL.

CHECK (quantity > 0) asks PostgreSQL to evaluate that expression for a new or changed row. A quantity of two passes; zero or minus one makes the expression false and the write fails. The same check applies to an update of an existing row. A previously valid line cannot be changed to quantity zero merely because it was accepted at insertion.

It would be easy to assume that the check also rejects a missing quantity. SQL’s treatment of missing values makes that assumption wrong. The comparison NULL > 0 has an unknown result, represented as SQL NULL. In PostgreSQL, a CHECK rejects a false result; a true or unknown result passes.

CREATE TABLE quantity_probe (
  quantity integer CHECK (quantity > 0)
);

INSERT INTO quantity_probe VALUES (2);    -- accepted
INSERT INTO quantity_probe VALUES (0);    -- rejected
INSERT INTO quantity_probe VALUES (NULL); -- accepted

The last insert is accepted. PostgreSQL has not decided that a missing quantity is positive. It has been given a check whose expression did not prove false. Combining NOT NULL with the positive check establishes the actual rule: a present quantity greater than zero.

A check can relate several fields in the same row. If a line had a discount amount, for example, a rule might compare it with that line’s price. But a PostgreSQL check is not a general query over the changing contents of other rows. The engine expects its answer to remain determined by the checked row. A lookup hidden in a function does not turn it into a dependable cross-table rule.

A condition also depends on the values its type permits. Our price checks exclude PostgreSQL’s NaN, or “not a number,” value. In current_price <> 'NaN'::numeric, <> means “not equal,” and ::numeric interprets the quoted value as a numeric value. PostgreSQL permits it in a numeric column and orders it above ordinary numbers, so a non-negative check alone would allow it. It cannot describe an agreed price.

The type does work before a check runs too. An integer rejects text that cannot be interpreted as an integer. Our decimal price type converts values to its declared scale, so checks examine that represented amount. If the application must reject an input with too many fractional digits instead of rounding it, that input rule needs its own treatment.

Unique values and keys

A positive quantity tells us something about one line. Identifying that line reliably requires comparing it with other records: no two lines may claim the same identity. This is the job of a key.

PRIMARY KEY (product_id) makes product IDs unique and non-missing. If there were two product records called P7, an order line referring to P7 would no longer identify one product. If the ID were missing, other records would have no usable value to refer to. Both parts of the primary-key guarantee matter.

The line’s key is the pair (order_id, line_no). PostgreSQL checks uniqueness of the pair, rather than requiring each column to be unique separately. We can have line 1 of O12 and line 1 of O13. We can also have several lines of O12. We cannot have two records both claiming to be line 1 of O12.

The product ID is not part of this primary key. Adding another line for P7 is allowed. The database enforces the identity we declared, rather than guessing from the fact that the same product appears twice.

INSERT INTO adds a row. Its named columns correspond to the VALUES in order: the first statement supplies order O12, line number 2, product P7, quantity 1 and agreed unit price €18. The following statements keep that mapping while changing a value that should be refused.

-- A second occurrence of P7 on another line is allowed.
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O12', 2, 'P7', 1, 18.00);

-- Each statement below is a separate attempted write.
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O12', 1, 'P7', 1, 18.00);
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O12', 3, 'P99', 1, 18.00);
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O12', 3, 'P7', 0, 18.00);
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O12', 3, 'P7', NULL, 18.00);

UNIQUE can protect another identifier without making it the primary key. The products table’s sku text UNIQUE says two assigned SKUs cannot be equal. The product ID remains the key other records use.

INSERT INTO products
  (product_id, name, current_price, sku)
VALUES ('P9', 'Small bowl', 12.00, NULL);
INSERT INTO products
  (product_id, name, current_price, sku)
VALUES ('P10', 'Another mug', 22.00, 'MUG-BLUE');

The first insert is accepted even though P8 also has a missing SKU. The second is rejected because P7 already has MUG-BLUE. By default, PostgreSQL’s unique constraint treats SQL nulls as distinct for this purpose. That fits our rule: an unassigned code is not a shared code belonging to two products.

If a business instead permits only one missing value, PostgreSQL offers UNIQUE NULLS NOT DISTINCT. If every product needs a code, we use NOT NULL as well. We choose the rule by its meaning, rather than treating one kind of uniqueness as the universal interpretation of absence.

Uniqueness also depends on how values are compared. A catalogue that treats MUG-BLUE and mug-blue as the same code needs to encode that policy; an ordinary text constraint does not automatically implement every business notion of sameness. Normalising codes before storage or choosing an appropriate expression and comparison is part of the design.

References and deletion

REFERENCES products (product_id) on an order line is a foreign key. It requires each present product ID to match a product record. With NOT NULL too, every line must name a real product. The check belongs to the database: writing P99 directly through a script still fails.

A foreign key refers to a declared unique key, so the match can identify at most one target record. A composite foreign key works the same way with a pair or larger combination. If a later shipment record identifies an order line, it must reference the pair (order_id, line_no). The combination has to match one line. In the two-order example from Designing a schema, O12 exists and line 2 exists under O13, but O12 has only line 1. Separately finding “O12” and “line 2” would therefore accept a shipment for a line that does not exist. Referencing the pair requires the actual record (O12, 2). The accepted insert above adds that record; before it runs, the pair is absent.

The reverse change matters too. Deleting P7 while lines still refer to it would leave dangling references. We declared ON DELETE RESTRICT, so that deletion fails. We can remove the product from sale by a separate catalogue state while keeping its identity for order history. Choosing to retain a product record is different from promising to sell it forever.

The customer reference uses the same restriction. Deleting C4 while O12 refers to C4 is refused. This is a retention decision in the example, not a complete policy for personal data. An application that needs to erase identifying details while preserving financial records must design those identities and retained fields accordingly.

For the relationship between orders and lines, we chose ON DELETE CASCADE. Deleting O12 automatically deletes its lines too. A line is a part of that order, so removing the order removes the contained records. This is convenient when intentionally deleting an unfinished order. It is also consequential: an accidental deletion of a completed order removes its purchase details.

A cascade expresses what deletion does; it does not decide who may delete a completed order. We still need permissions and operations that respect the shop’s retention requirements. Nor would a cascade refund a payment or remove an email already sent.

ON DELETE SET NULL is another option when a reference is genuinely optional after the target is removed. It would not work for our required product reference: setting it to null would fail NOT NULL. Before selecting an action, decide what the surviving record would mean without its reference.

PostgreSQL’s default NO ACTION also rejects a remaining broken reference when it is checked. It differs from RESTRICT in allowing the check to be deferred when the constraint is declared deferrable. Our constraints are checked immediately; a design that temporarily breaks a reference inside a transaction needs to consider check timing explicitly.

Concurrent writes

The application may check a SKU before inserting a product, so it can show a helpful message if the code is taken. Suppose a request checks for CUP-GREEN and finds no product.

SELECT product_id
FROM products
WHERE sku = 'CUP-GREEN';

-- The application inserts if the SELECT returned no rows.
INSERT INTO products
  (product_id, name, current_price, sku)
VALUES ('P11', 'Green cup', 16.00, 'CUP-GREEN');

Another request can run the same check before the first request inserts. It too sees no product and decides the code is available. The two checks were both accurate at the moments they ran. Neither reserved the code or established that it would still be available when the insert arrived.

Without a uniqueness rule, both writers can insert their product, producing the duplicate the application meant to prevent. Moving the same check into every caller does not close the interval between checking and writing.

The unique constraint governs the competing inserts themselves. A transaction groups database work that can be committed (kept) or rolled back (undone). In PostgreSQL, if the first transaction has inserted the code but has not finished, the second conflicting insert can wait for it. If the first commits, the second cannot also commit an insert with that code and receives a uniqueness error. If the first rolls back, the second may proceed. The application must handle the actual insert outcome, even if its earlier check succeeded.

This gives us a precise guarantee: two accepted product records cannot both have the same assigned SKU under the declared comparison. It does not mean every concurrent operation becomes safe. A uniqueness constraint solves this duplicate problem because the rule can be stated directly over stored values.

The wider operation

We can insert an order for C4 with no lines. The customer reference is valid, the placement time is present, and the order ID is unique. No constraint we declared requires that a line exist. Foreign keys check from each line to its order; they do not require every order to be referenced by a line.

Even a non-empty order may be unfinished. Checkout might need to add its lines and reduce stock together. Each individual statement can satisfy all of its constraints while a failure between statements leaves only part of the purchase recorded. A transaction can make those included changes succeed or be undone as one unit. It does not discover which steps constitute a purchase; the application must put the appropriate steps into it.

Stock adds another question. A rule such as CHECK (available >= 0) on a stock row can reject a stored negative quantity. It cannot establish that an earlier stock read was still current, or that the number of units added to an order matches the units deducted from stock. Two buyers reading the last available mug need a coordinated decision and change. The checkout example follows that problem in detail.

Likewise, our non-negative agreed price does not prove that the customer accepted it, that tax was calculated correctly, or that payment succeeded. Those are facts about the wider process. A database constraint can enforce a representation of a rule; it cannot observe events we have not connected to that representation.

Constraints are still valuable at this boundary. They make whole classes of bad states impossible through ordinary writes, and let application code rely on those guarantees. A transaction can then use keys, references, and valid quantities as building blocks while handling the larger operation.

Our next design problem is different. The mug has a capacity, the bowl has a diameter, and new products will bring new attributes. Tables and JSON explores where those facts should live, and which of these guarantees we can keep when their shape varies.