PostgreSQL can reject a write that violates a rule you have encoded in the database schema. The practical move is to express the business invariant as a constraint, so inserts and updates from any application path must satisfy it—not just the one you remembered to validate.
That “lie” is a value that breaks a defined rule, not necessarily a deliberate falsehood. Constraints cannot determine whether data is true in the real world; they can enforce precise conditions you specify.
As an Amazon Associate I earn from qualifying purchases.
What it means to make PostgreSQL refuse a lie
A constraint makes a data rule part of the table definition. PostgreSQL’s version 18 documentation says that a write violating a constraint raises an error, rather than storing the offending row. That gives the rule a single enforcement point even when data arrives through different application code paths. PostgreSQL 18: Constraints
Start with the invariant in ordinary language: for example, “a product price must be zero or greater.” Then choose the constraint whose scope matches that statement. A schema rule can reject a value that is impossible under your model; it cannot verify that the price matches a real-world invoice or that a person entered an honest name.
#1 Best Overall
Choose the constraint that matches the rule
| Rule | Constraint | What it enforces |
|---|---|---|
| A value must be present | NOT NULL |
Rejects null for that column. |
| A row’s value or combination of values must meet a condition | CHECK |
Evaluates a condition against the row being inserted or updated. |
| A value or combination must not repeat | UNIQUE |
Rejects duplicate key values according to the constraint’s null semantics. |
| A table needs a unique, non-null row identifier | PRIMARY KEY |
Combines uniqueness and non-null requirements; a table can have only one primary key. |
| A reference must identify an existing key in another table | FOREIGN KEY |
Maintains referential integrity, subject to null behavior and the declared update/delete action. |
| Two rows must not conflict under selected operators | EXCLUDE |
Requires at least one specified operator comparison for each row pair to be false or null. |
A primary key is usually a useful row identifier, but PostgreSQL does not require every table to have one. Both primary keys and unique constraints are backed by indexes that enforce their uniqueness; a primary key gets a unique B-tree index. A foreign key does not automatically create an index on its referencing columns. PostgreSQL 18: Constraints
Write the invariant as a row-level check
For a simple numeric rule, a check constraint can make the invariant explicit in the schema:
Rank #2
CREATE TABLE products (
product_id bigint PRIMARY KEY,
price numeric NOT NULL,
CONSTRAINT products_price_nonnegative CHECK (price >= 0)
);Now a write with a negative price violates the check and PostgreSQL raises an error:
INSERT INTO products (product_id, price)
VALUES (1, -5.00);The example uses two distinct rules. NOT NULL rejects a missing price, while CHECK (price >= 0) rejects a negative one. This distinction matters because PostgreSQL treats a check expression that evaluates to null as satisfied. A check alone therefore does not guarantee presence.
Use other constraint types for relationships and conflicts
Prevent duplicate values
Use UNIQUE when a value or combination of values must not be duplicated. PostgreSQL creates an index to enforce uniqueness. Be deliberate about whether null values are meaningful in the column, because uniqueness semantics concern key values and do not make a column required; use NOT NULL separately when every row needs a value.
Require a referenced row
A foreign key is the appropriate tool when a row must refer to an existing key in another table. The referenced columns must be backed by a primary key, unique constraint, or non-partial unique index. By default, a referencing row can satisfy the foreign key with null in its referencing column or columns, so pair the foreign key with NOT NULL if an absent reference is not allowed. For a composite reference, MATCH FULL requires the referencing columns to be either all null or all non-null.
Choose the foreign key’s update and delete behavior to match the relationship’s lifecycle. Also consider an index on the referencing columns: PostgreSQL does not create one automatically, and it can help when referenced rows are updated or deleted. PostgreSQL 18: Constraints
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallPrevent pairwise conflicts
An exclusion constraint can express conflicts that ordinary uniqueness cannot, such as two rows whose values overlap under chosen operators. It is suited to rules about pairs of rows, rather than a simple condition on one row. Choose the operators to represent the conflict precisely; the constraint requires that, for every pair, at least one specified comparison be false or null.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Know what a check constraint cannot guarantee
A CHECK constraint is for a condition on the row being checked. Do not use a check expression that queries other rows or tables to enforce a cross-row invariant: PostgreSQL documents that such checks are not a reliable constraint mechanism. Use a suitable unique, exclusion, or foreign-key constraint when it can model the rule. PostgreSQL 18: Constraints
The key design question is scope: is the rule about one value, one row, a unique key, a related row, or a pair of rows? Selecting a constraint at the same scope as the invariant lets PostgreSQL enforce what it can actually guarantee, without suggesting that the database knows whether arbitrary input is truthful.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →
