Cascade Delete

Dear Prfessional
Right now I have 190 tables and there are millions of data in the database
but now I want to physically delete the Personnel from the database and when
I started delete from master table so it gave me the constraint error and in
100 of tables there are reference that personnelID, I didn't apply cascade
delete at the time of designing so is it possible that in the single query I
enabled the cascade and after that disable it, can any body tell me the
short cut of that.
Waiting for your reply
Noor
Noor,
You have some options here. The first one would be to replace your existing FK contraints with ones with ON DELETE CASCADE in them. I wouldn't recommend this though as cascade deletes should be *designed* in to your application. This could be a dangerous
route to follow as some data may be deleted by accident.
The best thing for you to do is write a query that shows you the IDs of all the other tables that need to be deleted by joining to the parent table. When you have confimed that, you can write your DELETE statement by joining to the parent. You need to sta
rt from the bottom of the hierarchy and work your way up. You can find the schema information by querying the INFORMATION SCHEMA views - or consult any database design documentation that you may have.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Ya I already done this before.
Thanks
Noor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:AA7F2418-973F-44E1-BA4D-39D81A94DC81@.microsoft.com...
> Noor,
> You have some options here. The first one would be to replace your
existing FK contraints with ones with ON DELETE CASCADE in them. I wouldn't
recommend this though as cascade deletes should be *designed* in to your
application. This could be a dangerous route to follow as some data may be
deleted by accident.
> The best thing for you to do is write a query that shows you the IDs of
all the other tables that need to be deleted by joining to the parent table.
When you have confimed that, you can write your DELETE statement by joining
to the parent. You need to start from the bottom of the hierarchy and work
your way up. You can find the schema information by querying the INFORMATION
SCHEMA views - or consult any database design documentation that you may
have.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk