![]() ![]() 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". Thus, the constraint is not triggered.I wrote a (recursive) function to delete any row based on its primary key. When deleting an entry, strapi first updates the child collection types by setting the foreign key to null and then deleting the entry. with DBeaver) but when deleting the entry over strapi, the constraint doesn’t seem to affect the child entries.Īfter debugging, I found the reason for this. After adding this constraint, I found that the constraint works when deleting an entry in my db directly (e.g. However, I assumed it should be possible to just add this constraint separately in my db schema. I know this feature is currently on the roadmap: Cascade delete - Roadmap | Product Roadmap ![]() I would like to have a foreign key constraint with a cascade on deletion, meaning when I delete the parent entry, I want to delete the child entry from the db as well. I have a Collection Type with a 1 to 1 relation and the corresponding underlying PostgreSQL schema. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |