Postgresql: Foreign Key/On Delete Cascade

PostgreSQL: FOREIGN KEY/ON DELETE CASCADE

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. :)

Hope this helps you :)

is postgresql nullable foreign key with cascade delete feature possible

You are already doing it correctly. If the foreign key column is NULL, referential integrity is not enforced for that row.

How to add on delete cascade constraints?

I'm pretty sure you can't simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to

• start a transaction,
• drop the foreign key,
• add a foreign key with on delete cascade, and finally
• commit the transaction

Repeat for each foreign key you want to change.

But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example

alter table public.scores
drop constraint scores_gid_fkey,
add constraint scores_gid_fkey
   foreign key (gid)
   references games(gid)
   on delete cascade;

If you don't know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see "Constraints"), or you can query the information schema.

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null

How to know if a foreign key has cascade on delete clause

You can look in pg_constraint:

postgres=# create table car (id int primary key, name text);
CREATE TABLE
postgres=# create table driver (id int, car int references car(id) on delete cascade);
CREATE TABLE
postgres=# select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype 
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid;
 connamespace |     conname     | child_table | parent_table | confdeltype 
--------------+-----------------+-------------+--------------+-------------
         2200 | driver_car_fkey | driver      | car          | c
(1 row)

This will show you all the foreign keys (along with their source and target tables) that do not have ON DELETE CASCADE:

select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid
where confdeltype <> 'c';

More information in the documentation

Disclosure: I work for EnterpriseDB (EDB)

PostgreSQL: FOREIGN KEY/ON DELETE CASCADE/TRIGGER

So I took the brute force approach.

ALTER TABLE Transaction_Results DROP CONSTRAINT Transaction_Results_Detail_DetailID_fkey ;
ALTER TABLE Transaction_Results ALTER COLUMN DetailID DROP NOT NULL;

 ALTER TABLE Transaction_Results ADD COLUMN DetailID_Archive integer NULL;

 CREATE OR REPLACE FUNCTION process_Detail_delete() RETURNS TRIGGER AS $Transaction_Results_trigger$
    BEGIN
        --
        -- Update a row in Transaction_Results to reflect the delete performed on Detail,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            UPDATE  Transaction_Results SET DetailID = NULL, DetailID_Archive =  OLD.DetailID where DetailID = OLD.DetailID ;
            RETURN OLD;

        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$Transaction_Results_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER Transaction_Results_trigger
AFTER  DELETE ON [Detail]
   FOR EACH ROW EXECUTE PROCEDURE process_Detail_delete();​

Delete rows with foreign key in PostgreSQL

To automate this, you could define the foreign key constraint with ON DELETE CASCADE.

I quote the the manual for foreign key constraints:

CASCADE specifies that when a referenced row is deleted, row(s)
referencing it should be automatically deleted as well.

Look up the current FK definition like this:

SELECT pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  conrelid = 'public.kontakty'::regclass  -- assuming public schema
AND    conname = 'kontakty_ibfk_1';

Then add or modify the ON DELETE ... part to ON DELETE CASCADE (preserving everything else as is) in a statement like:

ALTER TABLE kontakty
   DROP CONSTRAINT kontakty_ibfk_1
 , ADD  CONSTRAINT kontakty_ibfk_1
   FOREIGN KEY (id_osoby) REFERENCES osoby (id_osoby) ON DELETE CASCADE;

There is no ALTER CONSTRAINT command. Drop and recreate the constraint in a single ALTER TABLE statement to avoid possible race conditions with concurrent write access.

You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVE lock on table kontakty and a SHARE ROW EXCLUSIVE lock on table osoby.

If you can't ALTER the table, then deleting by hand (once) or by trigger BEFORE DELETE (every time) are the remaining options.

Foreign key Constraint on delete cascade does not work postgres

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

---

Related Topics