PostgreSQL Lesson 12 – NOT NULL, DEFAULT & CHECK | Dataplexa

NOT NULL, DEFAULT & CHECK

Lesson 11 introduced all six constraint types. This lesson goes deeper on three of the most frequently used ones — NOT NULL, DEFAULT, and CHECK. These three work together to control exactly what values are allowed into your columns. You will see how to use them individually, how they interact with each other, how to apply them to existing tables, and how they behave with real data from the Dataplexa Store.

NOT NULL — Guaranteeing a Value Always Exists

When you mark a column NOT NULL you are telling PostgreSQL — this piece of information is mandatory. Every row must provide it. The moment an INSERT or UPDATE tries to leave that column empty, PostgreSQL stops the operation immediately with a clear error. This protects you from silent data quality problems that would otherwise only surface later when your application tries to use the missing value.

The decision of whether to mark a column NOT NULL comes down to one question — can this piece of information ever legitimately not exist? A customer's email address? Always required. Their middle name? Truly optional — leave it nullable. Their order date? Always required — an order without a date is meaningless.

-- Applying NOT NULL when creating a table
CREATE TABLE customers (
  id          SERIAL        PRIMARY KEY,
  first_name  VARCHAR(50)   NOT NULL,     -- required — every customer has a first name
  last_name   VARCHAR(50)   NOT NULL,     -- required
  email       VARCHAR(100)  NOT NULL,     -- required — used for login and contact
  middle_name VARCHAR(50),               -- optional — can be NULL
  phone       VARCHAR(20)                -- optional — not every customer provides this
);

-- What happens when you try to skip a NOT NULL column
INSERT INTO customers (first_name, email)
VALUES ('Alice', 'alice@example.com');   -- last_name is missing — fails

ERROR: null value in column "last_name" of relation "customers"
violates not-null constraint
DETAIL: Failing row contains (1, Alice, null, alice@example.com, null, null).

Adding and Removing NOT NULL on Existing Tables

You can add or remove NOT NULL from any column at any time using ALTER TABLE. When adding NOT NULL to a column that already has rows, PostgreSQL checks that no existing rows have a NULL in that column first. If any do, the operation fails — you need to fill those in before enforcing the constraint.

-- Add NOT NULL to an existing column
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

-- Remove NOT NULL from a column (make it optional again)
ALTER TABLE customers ALTER COLUMN phone DROP NOT NULL;

-- Safe approach: fill in NULLs first, then add the constraint
UPDATE customers SET phone = 'N/A' WHERE phone IS NULL;
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

ALTER TABLE
ALTER TABLE
UPDATE 12
ALTER TABLE

DEFAULT — Smart Fallback Values

A DEFAULT gives a column a fallback value that PostgreSQL uses automatically when no value is provided during an INSERT. This keeps your INSERT statements shorter and ensures every row starts with a sensible value — no manual effort required from the application.

Defaults are not just for simple values like 0 or TRUE. You can use any expression that PostgreSQL can evaluate at insert time — current timestamp, a generated string, or a function call. This makes defaults very powerful for audit columns, status tracking, and anything that should initialise automatically.

CREATE TABLE orders (
  id            SERIAL        PRIMARY KEY,
  customer_id   INTEGER       NOT NULL,
  status        VARCHAR(20)   NOT NULL  DEFAULT 'pending',     -- new orders start as pending
  total_amount  NUMERIC(10,2)           DEFAULT 0.00,          -- starts at zero
  is_priority   BOOLEAN                 DEFAULT FALSE,         -- not priority by default
  created_at    TIMESTAMPTZ             DEFAULT NOW(),         -- auto timestamp
  notes         TEXT                    DEFAULT ''             -- empty string instead of NULL
);

-- Insert without specifying defaults
INSERT INTO orders (customer_id) VALUES (1);

-- See what defaults filled in
SELECT id, customer_id, status, total_amount, is_priority, created_at
FROM orders WHERE customer_id = 1;

Overriding a Default

A default is just a fallback — you can always override it by providing a value explicitly. You can also use the keyword DEFAULT in an INSERT statement to explicitly request the default value, which is useful when you want to be clear in your code that you are intentionally using the default.

-- Override the default status for a rush order
INSERT INTO orders (customer_id, status, is_priority)
VALUES (2, 'processing', TRUE);

-- Use DEFAULT keyword explicitly in INSERT
INSERT INTO orders (customer_id, status, total_amount)
VALUES (3, DEFAULT, 125.50);   -- status uses its default 'pending'

SELECT id, customer_id, status, is_priority FROM orders ORDER BY id;

id | customer_id |  status     | is_priority
----+-------------+------------+-------------
  1 |           1 | pending    | f
  2 |           2 | processing | t
  3 |           3 | pending    | f

Changing and Removing a Default

-- Change the default value of an existing column
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'received';

-- Remove the default entirely — column becomes nullable with no fallback
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

-- Verify the change
SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'status';

ALTER TABLE
ALTER TABLE

column_name | column_default
-------------+----------------
status |

CHECK — Enforcing Business Rules at the Database Level

A CHECK constraint is a custom validation rule written as a SQL boolean expression. PostgreSQL evaluates it on every INSERT and UPDATE. If the expression returns false for a row, the operation is rejected. This is how you bake business rules directly into the database — so they are enforced no matter which application, script, or user modifies the data.

Any valid SQL boolean expression works in a CHECK — comparisons, ranges, IN lists, LIKE patterns, and even combinations using AND and OR.

CREATE TABLE products (
  id          SERIAL        PRIMARY KEY,
  name        VARCHAR(100)  NOT NULL,
  price       NUMERIC(10,2) NOT NULL   CHECK (price > 0),
  stock_qty   INTEGER       DEFAULT 0  CHECK (stock_qty >= 0),
  rating      NUMERIC(2,1)             CHECK (rating BETWEEN 1.0 AND 5.0),
  category    VARCHAR(50)              CHECK (category IN (
                                         'Electronics', 'Office', 'Furniture', 'Accessories'
                                       )),
  discount    NUMERIC(5,2)             CHECK (discount >= 0 AND discount <= 100)
);

-- Try inserting invalid data
INSERT INTO products (name, price, stock_qty)
VALUES ('Test Item', -10.00, 5);

ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (1, Test Item, -10.00, 5, null, null, null).

Naming CHECK Constraints

When a CHECK fails, PostgreSQL shows the constraint name in the error message. If your constraint is unnamed, the auto-generated name is cryptic. If you name it yourself, the error message is immediately meaningful — both for debugging and for showing useful messages to your users.

CREATE TABLE employees (
  id      SERIAL        PRIMARY KEY,
  name    VARCHAR(100)  NOT NULL,
  salary  NUMERIC(10,2) NOT NULL,
  age     INTEGER       NOT NULL,
  CONSTRAINT salary_must_be_positive   CHECK (salary > 0),
  CONSTRAINT salary_within_budget      CHECK (salary <= 250000),
  CONSTRAINT age_must_be_adult         CHECK (age >= 18),
  CONSTRAINT age_realistic             CHECK (age <= 80)
);

-- Try inserting an underage employee
INSERT INTO employees (name, salary, age) VALUES ('Alex', 55000, 16);

ERROR: new row for relation "employees" violates check constraint "age_must_be_adult"
DETAIL: Failing row contains (1, Alex, 55000.00, 16).

Much more useful than a cryptic auto-generated constraint name. The error tells you exactly which rule was broken.

CHECK Across Multiple Columns

A CHECK constraint can reference more than one column in the same table. This lets you enforce rules that involve a relationship between columns — for example, the end date of a promotion must always be after the start date.

CREATE TABLE promotions (
  id            SERIAL        PRIMARY KEY,
  name          VARCHAR(100)  NOT NULL,
  discount_pct  NUMERIC(5,2)  NOT NULL  CHECK (discount_pct > 0 AND discount_pct <= 90),
  start_date    DATE          NOT NULL,
  end_date      DATE          NOT NULL,
  CONSTRAINT valid_date_range CHECK (end_date > start_date)  -- end must be after start
);

-- Try inserting a promotion where end is before start
INSERT INTO promotions (name, discount_pct, start_date, end_date)
VALUES ('Summer Sale', 20.00, '2024-08-01', '2024-07-01');  -- fails

ERROR: new row for relation "promotions" violates check constraint "valid_date_range"
DETAIL: Failing row contains (1, Summer Sale, 20.00, 2024-08-01, 2024-07-01).

How NOT NULL, DEFAULT, and CHECK Work Together

These three constraints complement each other perfectly. NOT NULL ensures a value is always provided. DEFAULT ensures a sensible value is used when none is given. CHECK ensures the value — whether provided by the user or filled by a default — meets your business rules. Together they form a tight validation layer right at the database level.

-- All three working together on the same column
CREATE TABLE order_items (
  id          SERIAL        PRIMARY KEY,
  order_id    INTEGER       NOT NULL,
  product_id  INTEGER       NOT NULL,
  quantity    INTEGER       NOT NULL  DEFAULT 1   CHECK (quantity > 0),   -- must exist, defaults to 1, must be positive
  unit_price  NUMERIC(10,2) NOT NULL              CHECK (unit_price > 0), -- must exist, must be positive
  discount    NUMERIC(5,2)            DEFAULT 0   CHECK (discount >= 0 AND discount <= 100) -- optional, defaults to 0, must be a valid percentage
);

CREATE TABLE

Lesson Summary

Constraint	Purpose	Key Behaviour
NOT NULL	Column must always have a value	Rejects INSERT/UPDATE that leaves column empty
DEFAULT	Fallback value when none is supplied	Auto-fills column — can use expressions like NOW()
CHECK	Custom rule every row must pass	Rejects rows where expression is false
Adding to existing table	ALTER TABLE ... ALTER COLUMN / ADD CONSTRAINT	Validates all existing rows before applying
Named constraints	Use CONSTRAINT keyword	Clearer errors, easy to drop by name later

🧪 Practice Questions

Answer based on what you learned in this lesson.

1. What ALTER TABLE syntax removes a NOT NULL constraint from an existing column?

2. Which PostgreSQL function is commonly used as a DEFAULT value to automatically record when a row was created?

3. Which SQL keyword is used inside a CHECK constraint to validate that a value falls within a range — for example a rating from 1 to 5?

4. Which keyword lets you give a custom name to a CHECK constraint?

5. Which keyword can you use inside an INSERT statement to explicitly request that a column uses its defined default value?

Next up: Modifying tables that already exist — adding columns, renaming, and restructuring with ALTER TABLE.

← Previous Course Index Next →

PostgreSQL