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.
2 14542
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;
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew DeFaria |
last post by:
I created the following .sql file to demonstrate a problem I'm having.
According to the manual:
If |ON DELETE CASCADE| is specified, and a row in the parent table
is deleted, then InnoDB...
|
by: Rob Panosh |
last post by:
Hello,
If I have table the following table structure:
Table Customer ( CustomerId Numeric(10,0) Not Null, ... )
Table CustomerOrders ( CustomerOrderId As Numeric(10,0) Not Null,...
|
by: P.B. via SQLMonster.com |
last post by:
I cannot execute my sql to create a table with ON DELETE CASCADE option.
Here is my sql:
CREATE TABLE Employees (Name Text(10) not null, Age number,
CONSTRAINT pkEmployees
PRIMARY KEY (Name)); ...
|
by: Jean-Christian Imbeault |
last post by:
Currently I have a table defined as such:
TAL=# \d internal_keywords
Table "public.internal_keywords"
Column | Type | Modifiers
---------+---------+-----------
keyword | text | not null...
|
by: hilz |
last post by:
Hi all
I am trying to create a relation between two tables.
this is the sql statement in question
alter table T1
add constraint relation_T1_to_T2
foreign key (T2_ID)
references T2 (ID) on...
|
by: Karl O. Pinc |
last post by:
Hi,
Thought perhaps some other eyes than mine can tell if I'm doing
something wrong here or if there's a bug somewhere. I've never
passed a ROWTYPE varaible to a function but I don't see where...
|
by: R.Welz |
last post by:
Hello.
I want to discuss a problem I have with my database design becourse I
feel I cannot decide wheather I am on the right way of doing things.
First of all, I am writing a literature and...
|
by: nekiv90 |
last post by:
Greetings,
I have to delete older policies and its related records in other
tables.
The deletion from the parent table will trigger the deletion of
relevant records from about 30 something...
|
by: bonneylake |
last post by:
Hey Everyone,
I was wondering if anyone could provide a tutorial or example on how to create a stored procedure that uses delete cascade to delete records from 2 tables? i have seen examples...
|
by: manjuns |
last post by:
create table A(a1 varchar(10))
create table B(b1 varchar(10), a1 varchar(10) not null)
alter table A add primary key(a1)
alter table B ADD CONSTRAINT "a_fkey" foreign key (a1) references A (a1)...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |