Hi All

I have quite a complex database (well I think it is) which consists of several related tables.

Now to date the only way I have performed a deletion is by individually removing the foreign key records first and then remove the primary key record.

Is their a more effective way of doing this?.. I was thinking of a stored procedure that checks all foreign key tables and if a record is referenced in the table perform a deletion... basically performing all of my individual deletions in one execution.

Your experience would be appreciated.

Rit

__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER

Hi Rit,

I think I've had this problem before and I'm thinking it might be because I designed something wrong in my relationships. Usually, if I have a foreign key referenced like say I have a table for Users and then a separate table for phone numbers and I reference the phone numbers table with a foreign key. I will set the key to cascade on updates and deletes so when I delete a user all the entries in the foreign key table will also be delete. However, if I also reference that same data elsewhere I won't be able to delete the primary key table entry because that foreign key is also being referenced in another table. I would humbly suggest reviewing your relationships and see if they really make sense.

Thanks,
Richard

This is a case where a triger on the master parent table may be usefull. Only on deletes. That way the code is in one place and you insure its executed with every/any delete method. Although I usually avoid triggers this is one exception for me.

I agree with rob as a last resort. There's also code-wise deletes, which are pretty simple and quick but sometimes hard to keep track of.

there is no need to add any sproc or any trigger for this situation , if you use constraints for your table .. If you use foreign key constraint with ON DELETE CASCADE , it will automatically delete all the referenced records in the referenced table ..

As you have said that you did it manually that shows how poorly your table is designed.

I will show it using two example table -
1. t1 has an 'id' and a 'name' column
2. t2 has 'id' and 'phone' column , t1.id is the foreign key for this table ..

CREATE TABLE t1
(
id int NOT NULL,
name varchar(30)
)
go

CREATE TABLE t2
(
id int,
phone varchar(12),
CONSTRAINT fk_1 FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE)
go

Now if you try to delete data from t1 which is referenced to t2 , it will delete all such refernces.
Now say we have following data

t1
id name
----------
1 Som
2 Manu

t2
id phone
-----------
1 78787878
1 76777666
2 76776677

and then we have executed following
DELETE FROM t1 WHERE id=1
go

it will delete 1 record from t1 and two records from t2 (which are referenced)

There may be one more situation, where you dont want to delete data from t1 which is referenced. In such situation just dont use ON DELETE CASCADE , use only FOREIGN KEY constraint , it will give error if any such data is deleted which has references in other table.

Thanks All for your input. First port of call is to look at the table design/ relationships.

Many thanks

Rit