Creating Tables and Constraints

Tables are the fundamental storage units in a relational database. PostgreSQL gives you a rich set of constraints to enforce data integrity at the database level, so bad data never reaches your application.

CREATE TABLE Syntax

CREATE TABLE products (
  id          serial        PRIMARY KEY,
  name        text          NOT NULL,
  price       numeric(10,2) NOT NULL CHECK (price >= 0),
  sku         text          UNIQUE,
  category_id integer       REFERENCES categories(id) ON DELETE SET NULL,
  created_at  timestamptz   NOT NULL DEFAULT now()
);

Primary Keys

A PRIMARY KEY uniquely identifies each row. It implicitly adds a NOT NULL and UNIQUE constraint, and creates a B-tree index. You can use serial (auto-increment integer) or uuid as primary key types.

-- Integer primary key
id serial PRIMARY KEY

-- UUID primary key (requires pgcrypto or pg 13+)
id uuid PRIMARY KEY DEFAULT gen_random_uuid()

NOT NULL

NOT NULL prevents a column from storing a null value. Add it to every column that must always have a value.

UNIQUE

UNIQUE ensures no two rows share the same value in that column (or combination of columns for table-level unique constraints).

-- Column-level
email text UNIQUE

-- Table-level composite unique
UNIQUE (first_name, last_name)

CHECK Constraints

CHECK validates that a value satisfies a boolean expression.

price   numeric CHECK (price >= 0),
rating  integer CHECK (rating BETWEEN 1 AND 5)

Foreign Keys

REFERENCES enforces referential integrity between tables. The ON DELETE clause controls what happens to child rows when the parent is deleted.

ON DELETE action	Behaviour
RESTRICT / NO ACTION	Prevent deletion if children exist (default)
CASCADE	Delete child rows automatically
SET NULL	Set the FK column to null
SET DEFAULT	Set the FK column to its default value

Altering and Dropping Tables

-- Add a column
ALTER TABLE products ADD COLUMN stock integer NOT NULL DEFAULT 0;

-- Drop a column
ALTER TABLE products DROP COLUMN sku;

-- Drop a table
DROP TABLE products;

-- Drop only if it exists
DROP TABLE IF EXISTS products;

Constraints applied at the database level catch errors regardless of which application or script is accessing the data, making your schema self-documenting and resilient.