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?
A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.
You are saying in a opposite way, this is not that when you delete from child table then records will be deleted from parent table.
UPDATE 1:
ON DELETE CASCADE option is to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behaviour of the database server prevents you from deleting data in a table if other tables reference it.
If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.
So it's all about what will happen when you delete rows from Parent table not from child table.
So in your case when user removes entries from categories
table then rows will be deleted from books table.