SQLDatabase Designintermediate40 min read

Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT

Enforce data integrity with NOT NULL, UNIQUE, CHECK, and DEFAULT constraints in PostgreSQL, including NULL behavior, business rules, and practical schema design.

On This Page (Table of Contents)

In this lesson, you will learn

✓Enforce mandatory values with NOT NULL constraints.
✓Prevent duplicate values with UNIQUE constraints and understand their relationship to unique indexes.
✓Validate business rules using CHECK constraints.
✓Assign default values when an INSERT statement omits a column.
✓Distinguish a missing value from an explicitly supplied NULL.
✓Combine constraints to protect data integrity at the database level.
✓Identify common constraint violations and interpret PostgreSQL errors.

1. What Are Database Constraints?

A database constraint is a rule that the database checks to protect the correctness of stored data.

Imagine an online store. Its database should not accept:
- A product without a required name.
- Two products with the same SKU when each SKU must identify one product.
- A negative product price.
- An order whose status is outside the allowed set of statuses.

If these rules exist only in frontend code, another client or a direct SQL query could bypass them. Database constraints provide a shared layer of protection.

This lesson uses PostgreSQL syntax and behavior.

We will study four constraints:

- **NOT NULL:** A value is required.
- **UNIQUE:** A value or combination of values must be unique.
- **CHECK:** A value must satisfy a condition.
- **DEFAULT:** A value is supplied when the INSERT statement omits the column or requests DEFAULT.

A default does not validate a value. For example, DEFAULT 0 does not prevent a user from inserting -5. A CHECK constraint is needed if negative values are forbidden.

2. NOT NULL: Require a Value

Use NOT NULL when a column must have a value.

For example, a product must have a name and a price.

Important distinction:
- Omitting a required column without a default generally causes an error.
- Explicitly inserting NULL into a NOT NULL column causes an error.
- An empty string ('') is not NULL, so NOT NULL alone does not reject it.

If blank names are forbidden as well, use an appropriate CHECK constraint in addition to NOT NULL.

Require product name and price

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE products_not_null (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
 
-- Valid row
INSERT INTO products_not_null
    (product_id, product_name, price)
VALUES
    (1, 'Keyboard', 1499.00);
 
SELECT product_id, product_name, price
FROM products_not_null;

Output

product_id | product_name | price -----------+--------------+-------- 1 | Keyboard | 1499.00

Both product_name and price are required. The primary key also requires product_id to be unique and non-NULL.

3. UNIQUE: Prevent Duplicate Values

A UNIQUE constraint prevents duplicate values in a column or duplicate combinations of values across multiple columns.

For example, if every product has a distinct SKU, declare sku UNIQUE.

PostgreSQL details:
- A UNIQUE constraint is commonly implemented using a unique index.
- By default, NULL values are considered distinct for uniqueness, so a single-column UNIQUE constraint can permit multiple NULL values.
- Add NOT NULL when the value must both exist and be unique.
- A composite UNIQUE constraint applies to the combination of its columns, not to each column independently.
- PostgreSQL supports UNIQUE NULLS NOT DISTINCT when NULL values should be treated as equal for uniqueness; this is a PostgreSQL-specific option.

A UNIQUE constraint is not a substitute for choosing the correct business key. The rule should match what the data means.

Require a unique SKU

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE products_unique (
    product_id INTEGER PRIMARY KEY,
    sku VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL
);
 
INSERT INTO products_unique
    (product_id, sku, product_name)
VALUES
    (1, 'KB-100', 'Keyboard'),
    (2, 'MS-200', 'Mouse');
 
SELECT product_id, sku, product_name
FROM products_unique
ORDER BY product_id;

Output

product_id | sku | product_name -----------+--------+------------- 1 | KB-100 | Keyboard 2 | MS-200 | Mouse

The example succeeds because both SKUs are distinct. Attempting to insert another row with sku = 'KB-100' violates the UNIQUE constraint.

4. CHECK: Enforce Business Rules

A CHECK constraint requires its expression not to evaluate to FALSE.

For example:
- price >= 0 prevents negative prices.
- age >= 18 enforces a minimum age in a table where that rule is appropriate.
- status IN ('pending', 'paid', 'cancelled') restricts a status to an allowed set.

Important NULL behavior: in PostgreSQL, a CHECK expression that evaluates to NULL (UNKNOWN) does not cause a violation. Therefore, CHECK (price >= 0) alone does not make price mandatory. Combine it with NOT NULL when the price must exist.

Choose business rules carefully. For example, a minimum age of 18 is appropriate only if that is the actual requirement for the application.

Validate price and product status

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE products_checked (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
        CHECK (price >= 0),
    status VARCHAR(20) NOT NULL
        CHECK (status IN ('active', 'inactive', 'discontinued'))
);
 
INSERT INTO products_checked
    (product_id, product_name, price, status)
VALUES
    (1, 'Monitor', 8999.00, 'active');
 
SELECT product_id, product_name, price, status
FROM products_checked;

Output

The row satisfies both CHECK expressions. A negative price or an unlisted status would be rejected.

5. DEFAULT: Supply an Omitted Value

DEFAULT specifies a value to use when an INSERT statement omits the column or explicitly requests DEFAULT.

For example, a new product may start with stock_quantity = 0 and status = 'active'.

Three cases are different:

1. Omit stock_quantity: the default is applied.
2. Specify DEFAULT for stock_quantity: the default is applied.
3. Explicitly specify NULL: NULL is attempted, not the default. If the column is NOT NULL, the INSERT fails.

A default can be a constant or an expression. In PostgreSQL, examples include CURRENT_TIMESTAMP and functions such as gen_random_uuid() when the function is available in the environment.

DEFAULT does not stop a caller from supplying another value. Use CHECK, NOT NULL, or other suitable constraints to enforce the actual rules.

Use a default for stock and status

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE products_default (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0
        CHECK (stock_quantity >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'inactive'))
);
 
-- Defaults are applied to omitted columns.
INSERT INTO products_default (product_id, product_name)
VALUES (1, 'Webcam');
 
-- An explicit value overrides the default.
INSERT INTO products_default
    (product_id, product_name, stock_quantity, status)
VALUES
    (2, 'Microphone', 15, 'active');
 
SELECT product_id, product_name, stock_quantity, status
FROM products_default
ORDER BY product_id;

Output

The first INSERT receives the defaults. The second explicitly supplies a stock quantity. Both rows satisfy the constraints.

6. Combine Constraints in a Realistic Table

Real schemas usually combine constraints because each one protects a different rule.

Consider a customer table:
- customer_id identifies the row.
- email must be present and unique.
- full_name must be present and not blank after trimming surrounding spaces.
- account_status has a default and an allowed set of values.
- created_at receives the current timestamp if omitted.

The example uses a named CHECK constraint for readability. Naming constraints makes error messages and later schema changes easier to manage.

Email normalization and case-insensitive uniqueness need a deliberate policy. A plain UNIQUE constraint on a PostgreSQL text column treats differently cased strings as distinct unless the schema uses a case-insensitive type, an expression-based unique index, or another suitable strategy.

Create a customer table with multiple constraints

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE customers (
    customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(254) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL
        CONSTRAINT customers_full_name_not_blank
        CHECK (length(trim(full_name)) > 0),
    account_status VARCHAR(20) NOT NULL DEFAULT 'active'
        CONSTRAINT customers_status_valid
        CHECK (account_status IN ('active', 'suspended', 'closed')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha Sharma');
 
SELECT
    customer_id,
    email,
    full_name,
    account_status,
    created_at
FROM customers;

Output

One customer row is returned. customer_id is generated by PostgreSQL, account_status defaults to 'active', and created_at receives the current timestamp. The exact ID and timestamp depend on the database state and execution time.

The table combines identity generation, uniqueness, required values, a named CHECK constraint, and defaults. The CHECK also rejects a name that becomes empty after trimming spaces.

7. Understand Constraint Violations

A constraint violation means the attempted write conflicts with a rule defined in the schema.

Typical PostgreSQL SQLSTATE codes include:
- 23502: not_null_violation
- 23505: unique_violation
- 23514: check_violation

Applications should handle expected errors appropriately. Do not rely only on frontend validation because other applications, scripts, concurrent requests, or administrative tools may also write to the database.

Avoid catching every database error and silently ignoring it. Distinguish a known constraint violation from connection failures, syntax errors, and other unexpected problems.

Examples of rejected writes

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Assume products_unique from the earlier section exists.
-- This fails because KB-100 already exists.
INSERT INTO products_unique
    (product_id, sku, product_name)
VALUES
    (3, 'KB-100', 'Another Keyboard');
 
-- Assume products_checked exists.
-- This fails because price cannot be negative.
INSERT INTO products_checked
    (product_id, product_name, price, status)
VALUES
    (2, 'Broken Monitor', -100.00, 'active');
 
-- Assume products_not_null exists.
-- This fails because product_name cannot be NULL.
INSERT INTO products_not_null
    (product_id, product_name, price)
VALUES
    (2, NULL, 500.00);

Output

Each INSERT is rejected by its corresponding constraint: UNIQUE, CHECK, or NOT NULL. Run each statement separately to inspect its error without interrupting the remaining statements in a client configured to stop on errors.

These are intentionally invalid statements. They demonstrate the type of data each constraint prevents.

8. Add a Constraint to an Existing Table

Constraints can also be added after a table has been created.

Before adding a constraint to existing data, inspect the rows. PostgreSQL will reject a new constraint if existing records violate it, unless you use a supported validation strategy.

The following example adds a named CHECK constraint. It assumes the table already exists and that all existing values satisfy the rule.

Add a named CHECK constraint

sql

1
2
3
4
5
6
7
8
ALTER TABLE products_default
ADD CONSTRAINT products_default_stock_nonnegative
CHECK (stock_quantity >= 0);
 
-- Inspect the current values.
SELECT product_id, product_name, stock_quantity
FROM products_default
ORDER BY product_id;

Output

The constraint is added if the existing rows satisfy stock_quantity >= 0. The SELECT returns the existing product records.

The example is safe to run once against the earlier products_default table, which already has a similar inline CHECK constraint. In a fresh exercise, use a separate table or omit the earlier inline constraint to avoid adding a redundant rule.

9. Common Mistakes

Mistake 1: Assuming NOT NULL rejects empty strings.
Correction: Empty strings are values, not SQL NULL. Add a suitable CHECK constraint if blank text is forbidden.

Mistake 2: Assuming UNIQUE always means only one row may have NULL.
Correction: PostgreSQL's default UNIQUE behavior treats NULL values as distinct. Use NOT NULL if the column is required.

Mistake 3: Assuming CHECK (price >= 0) rejects NULL.
Correction: CHECK accepts expressions that evaluate to UNKNOWN. Add NOT NULL if price must be present.

Mistake 4: Assuming DEFAULT replaces explicitly supplied NULL.
Correction: A default applies when a column is omitted or DEFAULT is requested, not when NULL is explicitly supplied.

Mistake 5: Using a default as if it were a validation rule.
Correction: DEFAULT 0 does not prevent a caller from inserting -10. Use CHECK (quantity >= 0).

Mistake 6: Treating UNIQUE as a business rule without considering case sensitivity.
Correction: Decide whether values such as Asha@example.com and asha@example.com should be treated as equivalent, and implement that policy explicitly.

Mistake 7: Adding constraints to existing data without checking it.
Correction: Find and resolve violating rows before applying a constraint that validates existing records.

10. Practice Exercises

Exercise 1:
Create a products table where product_name is mandatory and sku must be unique and mandatory.

Exercise 2:
Add a CHECK constraint that prevents a product's price from being negative.

Exercise 3:
Create an orders table whose status defaults to 'pending' and only accepts 'pending', 'paid', or 'cancelled'.

Exercise 4:
Explain why DEFAULT 0 and CHECK (stock_quantity >= 0) solve different problems.

Exercise 5:
Explain what happens when NULL is explicitly supplied to a NOT NULL column with a DEFAULT value.

Exercise 6:
In PostgreSQL, why can a UNIQUE column contain more than one NULL by default?

Exercise 7:
Choose appropriate constraints for a table where quantity must be supplied and must be greater than zero.

Try writing the SQL before reading the answer below.

Practice solution: inventory items

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE inventory_items (
    item_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku VARCHAR(40) NOT NULL UNIQUE,
    item_name VARCHAR(120) NOT NULL
        CHECK (length(trim(item_name)) > 0),
    unit_price NUMERIC(10, 2) NOT NULL
        CHECK (unit_price >= 0),
    quantity INTEGER NOT NULL DEFAULT 0
        CHECK (quantity >= 0),
    item_status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (item_status IN ('active', 'inactive'))
);
 
INSERT INTO inventory_items (sku, item_name, unit_price)
VALUES ('INV-100', 'USB Hub', 799.00);
 
SELECT item_id, sku, item_name, unit_price, quantity, item_status
FROM inventory_items;

Output

One row is returned with sku INV-100, item_name USB Hub, unit_price 799.00, quantity 0, and item_status active. The generated item_id depends on the table's identity sequence state.

The example combines the four constraints. The default quantity is valid because it also satisfies the non-negative CHECK.

11. Knowledge Check

Test your understanding of required values, uniqueness, validation, and defaults.

12. Interview Questions

Q1. What is a database constraint?
Answer: A rule enforced by the database to preserve data integrity.

Q2. What is the difference between NOT NULL and DEFAULT?
Answer: NOT NULL rejects NULL values. DEFAULT supplies a value when a column is omitted or DEFAULT is requested.

Q3. Does UNIQUE automatically mean NOT NULL?
Answer: No. In PostgreSQL, a unique column can contain multiple NULL values by default.

Q4. Can a CHECK constraint reject NULL by itself?
Answer: Not necessarily. A CHECK expression that evaluates to UNKNOWN because of NULL does not itself cause a violation. Use NOT NULL when a value is required.

Q5. What is a composite UNIQUE constraint?
Answer: A uniqueness rule applied to the combination of multiple columns.

Q6. How does PostgreSQL commonly implement a UNIQUE constraint?
Answer: It creates a unique index to enforce uniqueness.

Q7. Can a default prevent negative values?
Answer: No. A default only supplies a value in specified insertion cases. Use CHECK to reject negative values.

Q8. Why enforce constraints in the database when the application validates input?
Answer: Database constraints protect integrity across all clients and write paths, not just one application's interface.

Q9. What does SQLSTATE 23502 mean?
Answer: A NOT NULL violation.

Q10. What should you check before adding a constraint to a populated table?
Answer: Check existing records for violations and plan how to correct them before enforcing the rule.

13. Lesson Summary

The four constraints serve different purposes:

- **NOT NULL:** Requires a value.
- **UNIQUE:** Prevents duplicate values or combinations under the database's uniqueness rules.
- **CHECK:** Enforces a Boolean condition on stored rows.
- **DEFAULT:** Supplies a value when an INSERT omits a column or requests DEFAULT.

When designing a table, identify the business rule first, then select the appropriate constraint. Combine constraints when necessary—for example, NOT NULL plus CHECK for a required non-negative price.

Finally, test both valid and invalid inserts. A good constraint design makes invalid states harder to store, even when data comes from outside the main application.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming DEFAULT replaces an explicitly inserted NULL

Why it happens: The default is confused with automatic NULL handling.

Incorrect Approach

sql

CREATE TABLE task_defaults (
    task_id INTEGER PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
);
 
-- This fails: explicit NULL is not replaced by the default.
INSERT INTO task_defaults (task_id, status)
VALUES (1, NULL);

Correct Solution

sql

-- Omit status to use its default.
INSERT INTO task_defaults (task_id)
VALUES (1);
 
-- Or explicitly request the default.
INSERT INTO task_defaults (task_id, status)
VALUES (2, DEFAULT);

Key takeaway: Defaults apply to omitted columns or an explicit DEFAULT keyword. They do not override explicitly supplied NULL.

#2 Assuming CHECK rejects NULL values

Why it happens: SQL's three-valued logic is overlooked.

Incorrect Approach

sql

CREATE TABLE prices_incorrect (
    price NUMERIC CHECK (price >= 0)
);
 
-- NULL can pass the CHECK.
INSERT INTO prices_incorrect (price)
VALUES (NULL);

Correct Solution

sql

CREATE TABLE prices_correct (
    price NUMERIC NOT NULL CHECK (price >= 0)
);

Key takeaway: A CHECK expression that evaluates to UNKNOWN does not fail the constraint. NOT NULL is needed when the value must be present.

#3 Assuming UNIQUE prevents multiple NULL values in PostgreSQL

Why it happens: Uniqueness is confused with mandatory-value validation.

Incorrect Approach

sql

CREATE TABLE users_incorrect (
    email VARCHAR(254) UNIQUE
);
 
-- PostgreSQL's default UNIQUE behavior permits multiple NULL values.
INSERT INTO users_incorrect (email) VALUES (NULL);
INSERT INTO users_incorrect (email) VALUES (NULL);

Correct Solution

sql

CREATE TABLE users_correct (
    email VARCHAR(254) NOT NULL UNIQUE
);

Key takeaway: Use NOT NULL together with UNIQUE when the value must exist and be unique.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT Knowledge Check

Test your understanding with immediate feedback

Question 1 of 6

Which constraint prevents SQL NULL in a column?

Question 2 of 6

What does UNIQUE enforce?

Question 3 of 6

What is the purpose of CHECK (price >= 0)?

Question 4 of 6

When is a DEFAULT value applied?

Question 5 of 6

Which definition makes quantity required, non-negative, and initially zero when omitted?

Question 6 of 6

Which PostgreSQL SQLSTATE usually identifies a UNIQUE violation?

Key takeaways

Constraints let the database reject data that violates defined integrity rules.
NOT NULL prevents a column from storing NULL.
UNIQUE prevents duplicate non-NULL values under PostgreSQL's default NULL-distinct behavior.
CHECK evaluates a Boolean expression and rejects rows when the expression is FALSE; an UNKNOWN result caused by NULL does not itself violate the constraint.
DEFAULT supplies a value when an INSERT omits a column or explicitly uses the DEFAULT keyword; it does not replace an explicitly supplied NULL.
A default is not a validation rule. Use constraints to enforce requirements.
Database constraints protect data even when writes come from different applications or scripts.
PostgreSQL commonly implements UNIQUE constraints with unique indexes.

Was this lesson helpful?

Found an error? Report an issue