PostgreSQL — Foreign Key

Basic Foreign Key

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
);

Named Foreign Key

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

ON DELETE Actions

-- CASCADE: Delete children
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- SET NULL: Set to NULL
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL

-- RESTRICT: Prevent deletion
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

ON UPDATE Actions

CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE

Add Foreign Key

ALTER TABLE orders ADD CONSTRAINT fk_user 
FOREIGN KEY (user_id) REFERENCES users(id);

Drop Foreign Key

ALTER TABLE orders DROP CONSTRAINT fk_user;

Mini Practice

1. Create foreign key
2. Use ON DELETE CASCADE
3. Use ON DELETE SET NULL
4. Add foreign key to existing table

Up Next

Continue with Functions — PostgreSQL functions.