Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →NOT NULL requires a column to have a value; CHECK tests whether a row satisfies a condition. A CHECK alone may still allow NULL, so use both when a value must be present and must meet a rule.
What does each constraint validate?
NOT NULL: presence
A NOT NULL constraint prevents an inserted or updated row from storing SQL NULL in that column. It does not restrict which non-null values are allowed. PostgreSQL 17 describes it as requiring that a column “must not assume the null value.” PostgreSQL 17: Constraints
CHECK: a condition
A CHECK constraint tests an expression against a row. It is useful for rules such as price > 0 or for relationships between columns in the same row. A false result violates the constraint, but how a database treats null or unknown results matters.
Does a CHECK constraint prevent NULL?
Not necessarily. In PostgreSQL 17, a CHECK is satisfied when its expression evaluates to true or null. If price is null, price > 0 evaluates to null rather than false, so CHECK (price > 0) does not by itself require a price. MySQL 8.4 likewise documents that a check condition may evaluate to TRUE or UNKNOWN, including for null values. PostgreSQL 17: Constraints · MySQL 8.4: CHECK Constraints
#1 Best Overall
Use NOT NULL when presence is required. A comparison such as price = NULL is not the right way to test for null; MySQL recommends IS NULL and IS NOT NULL for null tests. MySQL 8.4: Problems with NULL Values
When should you use one, or both?
- Use
NOT NULLwhen the rule is simply that the field must be supplied. - Use
CHECKwhen only certain values are valid, or when values in a row must satisfy a relationship. - Use both when a value must be present and must meet a condition.
For example, this PostgreSQL-style table definition requires a name and a price, and also requires the price to be greater than zero:
CREATE TABLE products (
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);A table-level check can express a relationship between columns in the same row, such as comparing a regular price with a discounted price. PostgreSQL’s documentation gives this kind of comparison as a check-constraint use case. PostgreSQL 17: Constraints
Why not replace NOT NULL with CHECK?
In PostgreSQL, NOT NULL is functionally equivalent to CHECK (column_name IS NOT NULL), but PostgreSQL says the explicit NOT NULL form is more efficient. It also states the presence requirement directly, making the schema easier to read. PostgreSQL 17: Constraints
Rank #3
What CHECK constraints cannot safely express
PostgreSQL assumes check conditions are immutable: for the same row, the expression is expected to keep producing the same result. Its documentation cautions that a check should not rely on data outside the row being checked. A rule involving other rows or tables therefore needs a different mechanism; do not treat CHECK as a general substitute for a foreign key, a uniqueness constraint, or a cross-row aggregate rule. PostgreSQL 17: Constraints
Database and version differences
Constraint behavior and syntax can vary by database and version. The PostgreSQL behavior described here is from PostgreSQL 17 documentation; the MySQL behavior is from MySQL 8.4 documentation, which also documents an enforcement option for check constraints. SQLite’s CREATE TABLE reference documents both NOT NULL and CHECK constraints, but that alone is not a basis for assuming every detail matches PostgreSQL or MySQL. Check the documentation for the exact engine and version you deploy. SQLite: CREATE TABLE
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.
