PostgreSQL — Constraints

NOT NULL

CREATE TABLE users (
    name VARCHAR(100) NOT NULL
);

UNIQUE

CREATE TABLE users (
    email VARCHAR(255) UNIQUE
);

PRIMARY KEY

CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

FOREIGN KEY

CREATE TABLE orders (
    user_id INTEGER REFERENCES users(id)
);

CHECK

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0)
);

DEFAULT

CREATE TABLE users (
    status VARCHAR(20) DEFAULT 'active'
);

Add Constraint

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Drop Constraint

ALTER TABLE users DROP CONSTRAINT unique_email;

Mini Practice

1. Add NOT NULL constraint
2. Add UNIQUE constraint
3. Add FOREIGN KEY
4. Add CHECK constraint

Up Next

Continue with Primary Key — primary key details.