SQLMentor // learn db2

Constraints

Difficulty: Intermediate · ~9 min read

Overview

Constraints are rules the database enforces on column values. They guarantee data quality at the storage layer — once a constraint is in place, no application bug, ad-hoc query, or admin slip can put bad data in.

DB2 supports six core constraint types:

Constraint Guarantees
NOT NULL Value must be present
UNIQUE No two rows share this value
PRIMARY KEY Combination of NOT NULL + UNIQUE — row's identity
FOREIGN KEY Value must exist in another table
CHECK Custom boolean condition must be true
DEFAULT A default value when none supplied (technically a column option, not a constraint)

Plus DB2-specific extras: generated columns, identity columns, and referential actions (ON DELETE CASCADE, etc.).

Syntax

CREATE TABLE name (
  col1 TYPE NOT NULL,
  col2 TYPE DEFAULT value,
  col3 TYPE UNIQUE,
  col4 TYPE CHECK (col4 > 0),
  PRIMARY KEY (col1),
  FOREIGN KEY (col5) REFERENCES other_table(col)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

You can also add constraints after the fact:

ALTER TABLE name ADD CONSTRAINT constraint_name <definition>;
ALTER TABLE name DROP CONSTRAINT constraint_name;

Examples

Example 1: NOT NULL + DEFAULT

CREATE TABLE customers (
  customer_id INTEGER     NOT NULL,
  email       VARCHAR(254) NOT NULL,
  status      VARCHAR(10)  NOT NULL DEFAULT 'ACTIVE',
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- This works — defaults fill the rest
INSERT INTO customers (customer_id, email) VALUES (1, 'a@x.com');

-- This fails — email is NOT NULL
INSERT INTO customers (customer_id) VALUES (2);

Output of the failing insert:

SQL0407N  Assignment of a NULL value to a NOT NULL column
"EMAIL" is not allowed.  SQLSTATE=23502

Example 2: PRIMARY KEY and UNIQUE

CREATE TABLE products (
  product_id  INTEGER      NOT NULL PRIMARY KEY,
  sku         VARCHAR(40)  NOT NULL UNIQUE,
  name        VARCHAR(80)  NOT NULL
);

INSERT INTO products VALUES (1, 'WID-001', 'Widget');
INSERT INTO products VALUES (2, 'WID-001', 'Other'); -- fails: duplicate SKU
INSERT INTO products VALUES (1, 'WID-002', 'Other'); -- fails: duplicate PK

A table can have one primary key but many unique constraints. Both create unique indexes automatically.

Example 3: FOREIGN KEY with CASCADE

CREATE TABLE orders (
  order_id    INTEGER NOT NULL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total       DECIMAL(11,2) NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
);

INSERT INTO customers (customer_id, email) VALUES (1, 'a@x.com');
INSERT INTO orders VALUES (100, 1, 49.99);

-- Deleting customer 1 cascades to their orders
DELETE FROM customers WHERE customer_id = 1;

SELECT COUNT(*) FROM orders WHERE customer_id = 1;  -- 0

Other referential actions: ON DELETE RESTRICT (default — block the delete), ON DELETE SET NULL, ON DELETE NO ACTION.

Example 4: CHECK constraint

CREATE TABLE inventory (
  product_id INTEGER       NOT NULL PRIMARY KEY,
  quantity   INTEGER       NOT NULL CHECK (quantity >= 0),
  price      DECIMAL(9,2)  NOT NULL,
  CONSTRAINT positive_price CHECK (price > 0)
);

INSERT INTO inventory VALUES (1, 10, 9.99);   -- ok
INSERT INTO inventory VALUES (2, -5, 9.99);   -- fails: quantity < 0
INSERT INTO inventory VALUES (3, 10, -1);     -- fails: positive_price

Naming your constraints (CONSTRAINT positive_price) means failures point you straight to the rule that fired.

Example 5: Identity column (auto-incrementing PK)

CREATE TABLE events (
  event_id INTEGER GENERATED ALWAYS AS IDENTITY
                   (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
  name     VARCHAR(80) NOT NULL,
  occurred TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

Output:

EVENT_ID  NAME    OCCURRED
--------  ------  --------------------------
       1  login   2026-05-14-09.42.11.293045
       2  logout  2026-05-14-09.42.12.117008

Example 6: Adding and dropping constraints

-- Add a CHECK to an existing column
ALTER TABLE inventory
  ADD CONSTRAINT chk_max_qty CHECK (quantity <= 100000);

-- Drop it
ALTER TABLE inventory
  DROP CONSTRAINT chk_max_qty;

-- Add a FK after the fact
ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Notes & Tips

  • DB2 enforces constraints at statement boundary — a single statement that temporarily violates is OK only if it ends in a valid state.
  • A PRIMARY KEY is essentially NOT NULL UNIQUE plus a hint that this is the row's identity. Many tools use PK info for replication, sync, and ORM mapping.
  • UNIQUE columns allow NULLs (unlimited NULLs are not considered duplicates of each other in DB2's default behaviour). To forbid NULLs too, add NOT NULL.
  • Avoid putting heavy expressions in CHECK — every insert/update re-evaluates them. Keep them simple boolean comparisons.
  • Foreign keys can hurt write performance on huge tables. For data warehouses, some teams disable FKs and re-enable for validation runs.
  • Always name your constraints for clearer error messages and easier ALTER TABLE ... DROP CONSTRAINT.

Practice Exercises

  1. Create a users table where email is unique, signup_date defaults to today, and age must be between 13 and 120 (use a CHECK constraint).
  2. Add a FOREIGN KEY from orders.user_id to users.user_id with ON DELETE SET NULL.
  3. Try inserting a row that violates each constraint and read the resulting SQLSTATE codes.
  4. Replace your PK with an IDENTITY column and re-test.
  5. Use ALTER TABLE ... DROP CONSTRAINT to remove the CHECK, then re-add it with a wider range.

Quick Quiz

Q1. What's the practical difference between PRIMARY KEY and UNIQUE?

Show answer

A PRIMARY KEY is NOT NULL and UNIQUE, and there can only be one per table. UNIQUE columns can be NULL (multiple NULLs are not considered duplicates of each other), and a table can have many of them. Both create unique indexes under the hood.

Q2. What does ON DELETE CASCADE do?

Show answer

When a row in the parent table is deleted, every dependent row in the child table (linked via the foreign key) is automatically deleted too. Useful for true ownership relationships (an order's order_items). Dangerous if misapplied — be very deliberate about using it.

Q3. What's the difference between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY?

Show answer

ALWAYS always generates the value — you cannot insert your own value into the column. BY DEFAULT generates it only when you don't supply one, which is useful for data migrations where existing IDs must be preserved.

Next Up

With constraints in place, our data is trustworthy. Next we learn to combine tables with joins.