|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Ciclic Foreign Keys?Scenario: -) I have four tables TableA, TableB, TableC and ProductTable. -) TableA is the main header table of TableB, TableB contains a reference to a 'Product' in table ProductTable. -) TableB is, in turn, a header table of TableC, TableC contains a reference to a 'Product' in table ProductTable. -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB to TableA, so if the row in TableA is deleted, it also deletes TableB's corresponding row(s). -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC to TableB, so if the row in TableB is deleted, it also deletes TableC's corresponding row(s). Now I want to add 2 more ForeignKeys from TablaB and TableC to the ProductTable with CASCADING DELETES, so if the product is deleted it will automatically delete corresponding rows from TableB and TableC. I can set the ForeignKey on *one* of the tables, but I can't set it on both as the servers informs me that this would cause a cyclic action. I can't see what the problem is. If TableB contains a reference to a particular product (lets call it 'ProductX') and TableC contains a reference to another product ('ProductY') there is not cyclic deletion. Can anyone help me workout what the problem is. TIA, MartinH. The problem is that there are multiple cascade paths from ProductTable to
TableC. There isn't any declarative way to specify that the reference between TableB and ProductTable and the reference between TableC and ProductTable can never refer to the same product, so SQL Server must assume that they can, and consequently prevents the referential action declaration. It looks like you're going to have to use a trigger to implement the cascade delete. Show quote "Martin Hart" <martin.harttur***@gmail.com> wrote in message news:OCJ2eE2kFHA.320@TK2MSFTNGP09.phx.gbl... > Hi: > > Scenario: > -) I have four tables TableA, TableB, TableC and ProductTable. > -) TableA is the main header table of TableB, TableB contains a > reference to a 'Product' in table ProductTable. > -) TableB is, in turn, a header table of TableC, TableC contains a > reference to a 'Product' in table ProductTable. > -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB > to TableA, so if the row in TableA is deleted, it also deletes TableB's > corresponding row(s). > -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC > to TableB, so if the row in TableB is deleted, it also deletes TableC's > corresponding row(s). > > Now I want to add 2 more ForeignKeys from TablaB and TableC to the > ProductTable with CASCADING DELETES, so if the product is deleted it > will automatically delete corresponding rows from TableB and TableC. > > I can set the ForeignKey on *one* of the tables, but I can't set it on > both as the servers informs me that this would cause a cyclic action. I > can't see what the problem is. If TableB contains a reference to a > particular product (lets call it 'ProductX') and TableC contains a > reference to another product ('ProductY') there is not cyclic deletion. > > Can anyone help me workout what the problem is. > > TIA, > MartinH. Brian:
Thanks, I now understand why, and thanks to your suggestion 'how' I can get around the problem. Thanks again. Martin. Brian Selzer escribió: Show quote > The problem is that there are multiple cascade paths from ProductTable to > TableC. There isn't any declarative way to specify that the reference > between TableB and ProductTable and the reference between TableC and > ProductTable can never refer to the same product, so SQL Server must assume > that they can, and consequently prevents the referential action declaration. > > It looks like you're going to have to use a trigger to implement the cascade > delete. > > > "Martin Hart" <martin.harttur***@gmail.com> wrote in message > news:OCJ2eE2kFHA.320@TK2MSFTNGP09.phx.gbl... > >>Hi: >> >>Scenario: >>-) I have four tables TableA, TableB, TableC and ProductTable. >>-) TableA is the main header table of TableB, TableB contains a >>reference to a 'Product' in table ProductTable. >>-) TableB is, in turn, a header table of TableC, TableC contains a >>reference to a 'Product' in table ProductTable. >>-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB >>to TableA, so if the row in TableA is deleted, it also deletes TableB's >>corresponding row(s). >>-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC >>to TableB, so if the row in TableB is deleted, it also deletes TableC's >>corresponding row(s). >> >>Now I want to add 2 more ForeignKeys from TablaB and TableC to the >>ProductTable with CASCADING DELETES, so if the product is deleted it >>will automatically delete corresponding rows from TableB and TableC. >> >>I can set the ForeignKey on *one* of the tables, but I can't set it on >>both as the servers informs me that this would cause a cyclic action. I >>can't see what the problem is. If TableB contains a reference to a >>particular product (lets call it 'ProductX') and TableC contains a >>reference to another product ('ProductY') there is not cyclic deletion. >> >>Can anyone help me workout what the problem is. >> >>TIA, >>MartinH. > > > I prefer to avoid cascading referential actions whenever possible because
they introduce an additional level of complexity into deadlock minimization and avoidance. It's better in my opinion to spell out the deletes or updates, either in an instead of trigger or in a stored procededure. This way I control the order in which locks are obtained, thus minimizing deadlocks. Show quote "Martin Hart" <martin.harttur***@gmail.com> wrote in message news:#f7DXN3kFHA.2472@TK2MSFTNGP15.phx.gbl... > Brian: > > Thanks, I now understand why, and thanks to your suggestion 'how' I can > get around the problem. > > Thanks again. > Martin. > > Brian Selzer escribió: > > The problem is that there are multiple cascade paths from ProductTable to > > TableC. There isn't any declarative way to specify that the reference > > between TableB and ProductTable and the reference between TableC and > > ProductTable can never refer to the same product, so SQL Server must assume > > that they can, and consequently prevents the referential action declaration. > > > > It looks like you're going to have to use a trigger to implement the cascade > > delete. > > > > > > "Martin Hart" <martin.harttur***@gmail.com> wrote in message > > news:OCJ2eE2kFHA.320@TK2MSFTNGP09.phx.gbl... > > > >>Hi: > >> > >>Scenario: > >>-) I have four tables TableA, TableB, TableC and ProductTable. > >>-) TableA is the main header table of TableB, TableB contains a > >>reference to a 'Product' in table ProductTable. > >>-) TableB is, in turn, a header table of TableC, TableC contains a > >>reference to a 'Product' in table ProductTable. > >>-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB > >>to TableA, so if the row in TableA is deleted, it also deletes TableB's > >>corresponding row(s). > >>-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC > >>to TableB, so if the row in TableB is deleted, it also deletes TableC's > >>corresponding row(s). > >> > >>Now I want to add 2 more ForeignKeys from TablaB and TableC to the > >>ProductTable with CASCADING DELETES, so if the product is deleted it > >>will automatically delete corresponding rows from TableB and TableC. > >> > >>I can set the ForeignKey on *one* of the tables, but I can't set it on > >>both as the servers informs me that this would cause a cyclic action. I > >>can't see what the problem is. If TableB contains a reference to a > >>particular product (lets call it 'ProductX') and TableC contains a > >>reference to another product ('ProductY') there is not cyclic deletion. > >> > >>Can anyone help me workout what the problem is. > >> > >>TIA, > >>MartinH. > > > > > >  |
|||||||||||||||||||||||
Other interesting topics