SQL Constraints

Constraints are rules a table enforces on its data. They stop bad data at the door: duplicate IDs, orders for customers who don't exist, negative prices or missing required values. A query can only be as accurate as the data underneath it, so constraints matter for analysts as well as engineers.

The six main constraints

Syntax

CREATE TABLE table_name (
  id      INT PRIMARY KEY,
  name    VARCHAR(100) NOT NULL,
  email   VARCHAR(100) UNIQUE,
  amount  NUMERIC(10,2) CHECK (amount >= 0),
  status  VARCHAR(20) DEFAULT 'Active',
  parent_id INT REFERENCES parent_table(id)
);

Example table

A returns table with every type of constraint:

CREATE TABLE returns (
  return_id     INT PRIMARY KEY,
  order_id      INT NOT NULL REFERENCES orders(order_id),
  reason        VARCHAR(100) NOT NULL,
  refund_amount NUMERIC(10,2) CHECK (refund_amount >= 0),
  return_date   DATE DEFAULT CURRENT_DATE,
  status        VARCHAR(20) DEFAULT 'Requested'
                CHECK (status IN ('Requested', 'Approved', 'Rejected', 'Refunded')),
  UNIQUE (order_id)        -- one return per order
);

INSERT INTO returns (return_id, order_id, reason, refund_amount)
VALUES (1, 1006, 'Size too small', 3798.00);

SELECT return_id, order_id, reason, status FROM returns;

Result:

(1 row)

status got its DEFAULT. Now let's see each constraint reject bad data.

PRIMARY KEY: no duplicate IDs

INSERT INTO returns (return_id, order_id, reason) VALUES (1, 1015, 'Damaged');

Error: duplicate key value violates unique constraint "returns_pkey"

FOREIGN KEY: the order must exist

INSERT INTO returns (return_id, order_id, reason) VALUES (2, 9999, 'Damaged');

Error: insert or update on table "returns" violates foreign key constraint "returns_order_id_fkey"

NOT NULL: reason is required

INSERT INTO returns (return_id, order_id) VALUES (2, 1015);

Error: null value in column "reason" of relation "returns" violates not-null constraint

CHECK: no negative refunds, only allowed statuses

INSERT INTO returns (return_id, order_id, reason, refund_amount)
VALUES (2, 1015, 'Damaged', -500);

Error: new row for relation "returns" violates check constraint "returns_refund_amount_check"

INSERT INTO returns (return_id, order_id, reason, status)
VALUES (2, 1015, 'Damaged', 'Lost');

Error: new row for relation "returns" violates check constraint "returns_status_check"

UNIQUE: one return per order

INSERT INTO returns (return_id, order_id, reason) VALUES (2, 1006, 'Changed mind');

Error: duplicate key value violates unique constraint "returns_order_id_key"

A valid row goes through

INSERT INTO returns (return_id, order_id, reason, refund_amount, status)
VALUES (2, 1015, 'Screen damaged', 28791.00, 'Approved');

SELECT return_id, order_id, reason, refund_amount, status FROM returns ORDER BY return_id;

Result:

(2 rows)

Foreign keys protect deletes too

You can't delete an order while other rows still point to it. Order 1015 has order items (and now a return), so this fails:

DELETE FROM orders WHERE order_id = 1015;

Error: update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"

Options when defining the foreign key: ON DELETE CASCADE (delete the returns too), ON DELETE SET NULL (clear the reference) or the default (refuse).

Composite keys and named constraints

A primary key can span several columns, and naming constraints makes errors easier to read:

CREATE TABLE cart_items (
  customer_id INT REFERENCES customers(customer_id),
  product_id  INT REFERENCES products(product_id),
  quantity    INT NOT NULL,
  CONSTRAINT pk_cart PRIMARY KEY (customer_id, product_id),
  CONSTRAINT chk_cart_qty CHECK (quantity BETWEEN 1 AND 10)
);

INSERT INTO cart_items VALUES (1, 102, 2), (1, 114, 1);
SELECT * FROM cart_items;

Result:

(2 rows)

The same customer can have many products, and the same product can be in many carts, but each (customer, product) pair appears once.

Common mistakes

• No primary key. Without one, duplicate rows creep in and can't be told apart.
• Relying on the app alone. Application bugs and manual imports bypass app checks; constraints catch everything.
• Using CHECK for cross-table rules. CHECK only sees the current row. Use foreign keys or triggers for rules involving other tables.

Practice question

Create a delivery_partners table where each partner has a unique ID, a required name, a unique phone number, and a rating between 1 and 5 that defaults to 5.

CREATE TABLE delivery_partners (
  partner_id   INT PRIMARY KEY,
  partner_name VARCHAR(100) NOT NULL,
  phone        VARCHAR(15) UNIQUE,
  rating       NUMERIC(2,1) DEFAULT 5 CHECK (rating BETWEEN 1 AND 5)
);

INSERT INTO delivery_partners (partner_id, partner_name, phone)
VALUES (1, 'Ravi Kumar', '9900112233');

SELECT * FROM delivery_partners;

Key takeaways

• Constraints enforce data rules inside the database.
• PRIMARY KEY identifies rows; FOREIGN KEY links tables safely.
• UNIQUE, NOT NULL and CHECK block duplicates, gaps and invalid values.
• DEFAULT fills in a value when none is supplied.