Use constraints to protect data

Constraints put durable rules beside the data. Every application, migration, script, and import hits the same wall.

For example:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

Each constraint has one job:

• NOT NULL requires a known value
• UNIQUE prevents two rows from using the same SKU
• CHECK rejects values outside a business rule
• the primary key gives every row a stable identity
• a foreign key protects a relationship with another table

Now this insert should fail:

INSERT INTO products (id, sku, name, price, stock)
VALUES (1, 'BOOK-1', 'SQL Notes', -4.00, 10);

The database rejects the row because -4.00 violates CHECK (price >= 0). The failure is useful. It stops an impossible price from becoming somebody else’s debugging session.

A second insert with the same SKU also fails, this time on UNIQUE. A third insert with a NULL name fails on NOT NULL.

NULL needs special attention. A check such as CHECK (price >= 0) can evaluate to unknown when price is NULL. SQL check constraints generally accept true or unknown. Pair the check with NOT NULL when absence is also invalid.

Application validation still matters because it can show friendly messages before sending a query. The database constraint is the final boundary when several code paths write to the same table.

Adding a constraint to an existing table can fail because old rows already violate it. Find and repair those rows before enforcing the rule. Run a SELECT that finds violating rows first, fix them, then add the constraint.

Try this on your own: attempt one valid and three invalid inserts against this table. Record which constraint rejects each invalid row.