November 5, 2009 at 11:38 am
I have a database and need to delete from 4 tables starting with the first and ending with the last ie:
Table1
pk_ColumnA
ColumnB
ColumnC
Table2
pk_ColumnA1
fk_ColumnA
ColumnB1
ColumnC1
Table3
pk_ColumnA2
fk_ColumnA1
ColumnB2
ColumnC2
Table4
fk_ColumnA2
ColumnB3
ColumnC3
When I delete the pk_ColumnA row, I'd like it to cascade all the way to fk_ColumnA2 in the 4th table.
I've looked online for a few hours and read through all the books I have and can't seem to figure this out. I think I need to create triggers from the top-down, but the example I tried didn't work, though I think it was for MySQL and not MSSSQL.
Any help would be much appreciated! Thanks!
November 5, 2009 at 12:00 pm
If you look under Create Table in Books Online, you'll find the syntax for creating a column constraint.
What you're looking for is "References <table> (<column>) on delete cascade".
Looks like this:
create table dbo.DropMe1 (
ID int identity primary key);
go
create table dbo.DropMe2 (
ID int identity primary key,
DropMe1ID int not null references dbo.DropMe1(ID) on delete cascade);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 12:06 pm
I found Cascading Referential Integrity Constraints, is that too much? Also, can I set the cascade to roll three tables deep?
Thanks again
November 5, 2009 at 1:17 pm
Try it and see. That's the best way to really know it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy