By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,515 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,515 IT Pros & Developers. It's quick & easy.

Delete cascade with FK

P: 1
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.
Mar 7 '12 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 700
Use ON DELETE CASCADE when you define foreign key.

See here
Mar 8 '12 #2

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. select delete_cascade('public','my_table','1');

Expand|Select|Wrap|Line Numbers
  1.     create or replace function delete_cascade(p_schema varchar, p_table varchar, p_key varchar, p_recursion varchar[] default null)
  2.      returns integer as $$
  3.     declare
  4.         rx record;
  5.         rd record;
  6.         v_sql varchar;
  7.         v_recursion_key varchar;
  8.         recnum integer;
  9.         v_primary_key varchar;
  10.         v_rows integer;
  11.     begin
  12.         recnum := 0;
  13.         select ccu.column_name into v_primary_key
  14.             from
  15.             information_schema.table_constraints  tc
  16.             join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
  17.             and tc.constraint_type='PRIMARY KEY'
  18.             and tc.table_name=p_table
  19.             and tc.table_schema=p_schema;
  21.         for rx in (
  22.             select kcu.table_name as foreign_table_name, 
  23.             kcu.column_name as foreign_column_name, 
  24.             kcu.table_schema foreign_table_schema,
  25.             kcu2.column_name as foreign_table_primary_key
  26.             from information_schema.constraint_column_usage ccu
  27.             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 
  28.             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
  29.             join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
  30.             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
  31.             where ccu.table_name=p_table  and ccu.table_schema=p_schema
  32.             and TC.CONSTRAINT_TYPE='FOREIGN KEY'
  33.             and tc2.constraint_type='PRIMARY KEY'
  34.     )
  35.         loop
  36.             v_sql := 'select '||rx.foreign_table_primary_key||' as key from '||rx.foreign_table_schema||'.'||rx.foreign_table_name||'
  37.                 where '||rx.foreign_column_name||'='||quote_literal(p_key)||' for update';
  38.             --raise notice '%',v_sql;
  39.             --found a foreign key, now find the primary keys for any data that exists in any of those tables.
  40.             for rd in execute v_sql
  41.             loop
  42.                 v_recursion_key=rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name||'='||rd.key;
  43.                 if (v_recursion_key = any (p_recursion)) then
  44.                     --raise notice 'Avoiding infinite loop';
  45.                 else
  46.                     --raise notice 'Recursing to %,%',rx.foreign_table_name, rd.key;
  47.                     recnum:= recnum +dallas.delete_cascade(rx.foreign_table_schema::varchar, rx.foreign_table_name::varchar, rd.key::varchar, p_recursion||v_recursion_key);
  48.                 end if;
  49.             end loop;
  50.         end loop;
  51.         begin
  52.         --actually delete original record.
  53.         v_sql := 'delete from '||p_schema||'.'||p_table||' where '||v_primary_key||'='||quote_literal(p_key);
  54.         execute v_sql;
  55.         get diagnostics v_rows= row_count;
  56.         --raise notice 'Deleting %.% %=%',p_schema,p_table,v_primary_key,p_key;
  57.         recnum:= recnum +v_rows;
  58.         exception when others then recnum=0;
  59.         end;
  61.         return recnum;
  62.     end;
  63.     $$
  64.     language PLPGSQL;
Sep 30 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.