CODEWITHSUNDEEP
sqlpostgresql
and-k
and-k

Reputation: 607

Foreign key Constraint on delete cascade does not work postgres

When I run DELETE FROM users WHERE id='some_id' the record on beta_keys table does not get deleted.

beta_keys table:

CREATE TABLE beta_keys (
    id serial PRIMARY KEY,
    key VARCHAR(60) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP
);

users table:

CREATE TABLE users (
    id serial PRIMARY KEY,
    email VARCHAR (256) UNIQUE NOT NULL,
    password VARCHAR (60) NOT NULL,
    beta_key_id INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    CONSTRAINT users_beta_key_id_fkey FOREIGN KEY (beta_key_id)
        REFERENCES beta_keys (id) MATCH SIMPLE
        ON DELETE CASCADE
);

Upvotes: 6

Views: 7483

Answers (1)

SqlZim
SqlZim

Reputation: 38023

users references beta_keys. delete cascade works by deleting referencing rows (users) when the referenced row (beta_keys) is deleted.

sqlfiddle: http://sqlfiddle.com/#!17/a7495/1

Upvotes: 8

Related Questions