PostgreSQL FOREIGN KEY ON DELETE CASCADE

Question

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

User · Answer

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 gt  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

User · Answer

In my humble experience with postgres 9 6  cascade delete doesn t work in practice for tables that grow above a trivial size    Even worse  while the delete cascade is going on  the tables involved are locked so those tables  and potentially your whole database  is unusable   Still worse  it s hard to get postgres to tell you what it s doing during the delete cascade   If it s taking a long time  which table or tables is making it slow   Perhaps it s somewhere in the pg stats information   It s hard to tell

User · Answer

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

User · Answer

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 CATs table then rows will be deleted from books table     Hope this helps you

[sql] PostgreSQL: FOREIGN KEY/ON DELETE CASCADE

Examples related to sql

Examples related to postgresql

Examples related to cascade