I have two tables like here:
DROP TABLE IF EXISTS schemas.book;
DROP TABLE IF EXISTS schemas.category;
DROP SCHEMA IF EXISTS schemas;
CREATE SCHEMA schemas;
CREATE TABLE schemas.category (
id BIGSERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
UNIQUE(name)
);
CREATE TABLE schemas.book (
id BIGSERIAL PRIMARY KEY,
published DATE NOT NULL,
category_id BIGINT NOT NULL REFERENCES schemas.category ON DELETE CASCADE ON UPDATE CASCADE,
author VARCHAR NOT NULL,
name VARCHAR NOT NULL,
UNIQUE(published, author, name),
FOREIGN KEY(category_id) REFERENCES schemas.category (id)
);
So the logic is simple, after user removes all book under category x, x gets removed from cats, i tried method above but doesn't work, after i clean table book, table category still populated, what's wrong?
This question is related to
sql
postgresql
cascade
PostgreSQL Forging Key DELETE, UPDATE CASCADE
CREATE TABLE apps_user(
user_id SERIAL PRIMARY KEY,
username character varying(30),
userpass character varying(50),
created_on DATE
);
CREATE TABLE apps_profile(
pro_id SERIAL PRIMARY KEY,
user_id INT4 REFERENCES apps_user(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
firstname VARCHAR(30),
lastname VARCHAR(50),
email VARCHAR UNIQUE,
dob DATE
);
Excerpt from PostgreSQL documentation:
Restricting and cascading deletes are the two most common options. [...]
CASCADE
specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
This means that if you delete a category – referenced by books – the referencing book will also be deleted by ON DELETE CASCADE
.
Example:
CREATE SCHEMA shire;
CREATE TABLE shire.clans (
id serial PRIMARY KEY,
clan varchar
);
CREATE TABLE shire.hobbits (
id serial PRIMARY KEY,
hobbit varchar,
clan_id integer REFERENCES shire.clans (id) ON DELETE CASCADE
);
DELETE FROM
clans will CASCADE
to hobbits by REFERENCES
.
sauron@mordor> psql
sauron=# SELECT * FROM shire.clans;
id | clan
----+------------
1 | Baggins
2 | Gamgi
(2 rows)
sauron=# SELECT * FROM shire.hobbits;
id | hobbit | clan_id
----+----------+---------
1 | Bilbo | 1
2 | Frodo | 1
3 | Samwise | 2
(3 rows)
sauron=# DELETE FROM shire.clans WHERE id = 1 RETURNING *;
id | clan
----+---------
1 | Baggins
(1 row)
DELETE 1
sauron=# SELECT * FROM shire.hobbits;
id | hobbit | clan_id
----+----------+---------
3 | Samwise | 2
(1 row)
If you really need the opposite (checked by the database), you will have to write a trigger!
In my humble experience with postgres 9.6, cascade delete doesn't work in practice for tables that grow above a trivial size.
Source: Stackoverflow.com