![]() Assuming your non-cascading foreign key constraints were created as 'ON DELETE NO ACTION DEFERRABLE', perform all the deletions explicitly in a single transaction or.Perform all the deletions explicitly in a single (potentially massive) query or.Perform all the deletions explicitly, one query at a time, starting with child tables (though this won't fly if you've got circular references) or.To delete non-cascading foreign key-protected child records and their referenced ancestors, your options include: Yeah, as others have said, there's no convenient 'DELETE FROM my_table. Of course, you should abstract stuff like that into a procedure, for the sake of your mental health. Testing=# alter table b add FOREIGN KEY (id) references a(id) on delete restrict Testing=# alter table b drop constraint b_id_fkey ![]() "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) ON DELETE CASCADE Testing=# alter table b add FOREIGN KEY (id) references a(id) on delete cascade Testing=# alter table b drop constraint b_a_id_fkey "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) Testing=# create table b (id integer references a) ĮRROR: update or delete on table "a" violates foreign key constraint "b_id_fkey" on table "b"ĭETAIL: Key (id)=(1) is still referenced from table "b". NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" If I understand correctly, you should be able to do what you want by dropping the foreign key constraint, adding a new one (which will cascade), doing your stuff, and recreating the restricting foreign key constraint.įor example: testing=# create table a (id integer primary key) raise notice 'Deleting %.% %=%',p_schema,p_table,v_primary_key,p_key Recnum:= recnum +delete_cascade(rx.foreign_table_schema::varchar, rx.foreign_table_name::varchar, rd.key::varchar, p_recursion||v_recursion_key) raise notice 'Recursing to %,%',rx.foreign_table_name, rd.key If (v_recursion_key = any (p_recursion)) then V_recursion_key=rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name||'='||rd.key found a foreign key, now find the primary keys for any data that exists in any of those tables. Where '||rx.foreign_column_name||'='||quote_literal(p_key)||' for update' V_sql := 'select '||rx.foreign_table_primary_key||' as key from '||rx.foreign_table_schema||'.'||rx.foreign_table_name||' Where ccu.table_name=p_table and ccu.table_schema=p_schema Join information_schema.key_column_usage kcu2 on nstraint_name=tc2.constraint_name and nstraint_catalog=tc2.constraint_catalog and nstraint_schema=tc2.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 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 tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema lumn_name as foreign_table_primary_keyįrom information_nstraint_column_usage ccu Select kcu.table_name as foreign_table_name, Join information_nstraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema Select ccu.column_name into v_primary_key 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) Please test it out and let me know how it works for you. ![]() It uses an array of data already marked for deletion to prevent infinite loops. I use this function VERY SPARINGLY anyway, I value my data too much to enable the cascading constraints on everything.īasically 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. Also, the keys all have to be able to be represented in string form, but it could be written in a way that doesn't have that restriction. 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. 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 wrote this because I did not want to create my constraints as "on delete cascade". I wrote a (recursive) function to delete any row based on its primary key. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |