Delete cascade with FK

[kamal sharma]

i have 3 table and they have Foreign key constraint.

Table1 pk Quote_id
Table2 pk order_id , Quote_id is foreign key for table 2
Table3 pk shipment_id, order_id is foreign key for table3

now if i delete a row in table 1 i want to delete cascade on teh row connected with foreign key constraints in the other tables.

[rski]

Use ON DELETE CASCADE when you define foreign key.

See here

http://www.postgresql.org/docs/9.1/static/sql-createtable.html

[collinph]

I wrote a (recursive) function to delete any row based on its primary key. I wrote this because I did not want to create my constraints as "on delete cascade". I wanted to be able to delete complex sets of data (as a DBA) but not allow my programmers to be able to cascade delete without thinking through all of the repercussions.


I'm still testing out this function, so there may be bugs in it -- but please don't try it if your DB has multi column primary (and thus foreign) keys. Also, the keys all have to be able to be represented in string form, which could be a bit dicey for primary keys that don't convert well to varchar, but in my case, nearly every PK is an integer, so I don't have much to worry about. I use this function VERY SPARINGLY anyway, I value my data too much to enable the cascading constraints on everything.


Basically this function is passed in the schema, table name, and primary value (in string form), and it will start by finding any foreign keys on that table and makes sure data doesn't exist-- if it does, it recursively calls itsself on the found data. It uses an array of data already marked for deletion to prevent infinite loops. Please test it out and let me know how it works for you. Note: It's a little slow.

I call it like so:

select delete_cascade('public','my_table','1');

    create or replace function delete_cascade(p_schema varchar, p_table varchar, p_key varchar, p_recursion varchar[] default null)
     returns integer as $$
    declare
        rx record;
        rd record;
        v_sql varchar;
        v_recursion_key varchar;
        recnum integer;
        v_primary_key varchar;
        v_rows integer;
    begin
        recnum := 0;
        select ccu.column_name into v_primary_key
            from
            information_schema.table_constraints  tc
            join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
            and tc.constraint_type='PRIMARY KEY'
            and tc.table_name=p_table
            and tc.table_schema=p_schema;

        for rx in (
            select kcu.table_name as foreign_table_name, 
            kcu.column_name as foreign_column_name, 
            kcu.table_schema foreign_table_schema,
            kcu2.column_name as foreign_table_primary_key
            from information_schema.constraint_column_usage ccu
            join information_schema.table_constraints tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema 
            join information_schema.key_column_usage kcu on kcu.constraint_name=ccu.constraint_name and kcu.constraint_catalog=ccu.constraint_catalog and kcu.constraint_schema=ccu.constraint_schema
            join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
            join information_schema.key_column_usage kcu2 on kcu2.constraint_name=tc2.constraint_name and kcu2.constraint_catalog=tc2.constraint_catalog and kcu2.constraint_schema=tc2.constraint_schema
            where ccu.table_name=p_table  and ccu.table_schema=p_schema
            and TC.CONSTRAINT_TYPE='FOREIGN KEY'
            and tc2.constraint_type='PRIMARY KEY'
    )
        loop
            v_sql := 'select '||rx.foreign_table_primary_key||' as key from '||rx.foreign_table_schema||'.'||rx.foreign_table_name||'
                where '||rx.foreign_column_name||'='||quote_literal(p_key)||' for update';
            --raise notice '%',v_sql;
            --found a foreign key, now find the primary keys for any data that exists in any of those tables.
            for rd in execute v_sql
            loop
                v_recursion_key=rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name||'='||rd.key;
                if (v_recursion_key = any (p_recursion)) then
                    --raise notice 'Avoiding infinite loop';
                else
                    --raise notice 'Recursing to %,%',rx.foreign_table_name, rd.key;
                    recnum:= recnum +dallas.delete_cascade(rx.foreign_table_schema::varchar, rx.foreign_table_name::varchar, rd.key::varchar, p_recursion||v_recursion_key);
                end if;
            end loop;
        end loop;
        begin
        --actually delete original record.
        v_sql := 'delete from '||p_schema||'.'||p_table||' where '||v_primary_key||'='||quote_literal(p_key);
        execute v_sql;
        get diagnostics v_rows= row_count;
        --raise notice 'Deleting %.% %=%',p_schema,p_table,v_primary_key,p_key;
        recnum:= recnum +v_rows;
        exception when others then recnum=0;
        end;

        return recnum;
    end;
    $$
    language PLPGSQL;