How to Create Protected Tables with Constraints in Postgres

Creating the Products Table

We'll build a table for a prop shop that sells items like laser blades, flux watches, and lightsabers. Each product needs a name, price, stock count, and a unique SKU code.

First, connect to the database:

sudo systemctl status postgresql
sudo -u postgres psql -d movie_store

Clear the console, then create the products table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT UNIQUE NOT NULL,
    price NUMERIC(6,2) NOT NULL CHECK (price > 0),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    in_stock BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

The id column uses SERIAL so Postgres handles it automatically. name uses TEXT instead of VARCHAR—both work similarly, but VARCHAR can limit character length. sku has a UNIQUE constraint so no two rows share the same SKU. price uses NUMERIC(6,2) for exact decimals (6 total digits, 2 after the decimal) with a CHECK to prevent negative prices. stock is an integer, defaults to 0, and also has a CHECK for non-negative values. in_stock is BOOLEAN (true/false), defaults to true. created_at uses TIMESTAMP and defaults to the current time via NOW().

Inserting Data

Insert some sample products:

INSERT INTO products (name, sku, price, stock) VALUES
('Laser Blade', 'LB-001', 99.99, 10),
('Flux Watch', 'FW-002', 149.50, 5),
('Lightsaber', 'LS-003', 299.99, 2),
('Sonic Screwdriver', 'SS-004', 79.99, 0),
('Invisibility Cloak', 'IC-005', 499.99, 1);

Five rows were inserted. Now view the table:

SELECT * FROM products;

Notice that in_stock and created_at were filled by their defaults. However, the "Sonic Screwdriver" has stock = 0 but in_stock = true—defaults don't know your business logic, they just fill gaps. You'd update that manually when inventory runs out.

Testing Constraints

Try inserting a duplicate SKU:

INSERT INTO products (name, sku, price, stock) VALUES
('Fake Blade', 'LB-001', 19.99, 1);

You get: duplicate key value violates unique constraint.

Now try a negative price:

INSERT INTO products (name, sku, price, stock) VALUES
('Cheap Item', 'CI-006', -5.00, 1);

You get: new row for relation "products" violates check constraint.

Finally, try omitting the name (which is NOT NULL):

INSERT INTO products (sku, price, stock) VALUES
('NN-007', 10.00, 1);

You get: null value in column "name" of relation "products" violates not-null constraint.

Three different rules, three different failures—all caught before bad data enters the database. This is the defensive layer of your database in production.