Yes, that turned out to be the magical secret. I scripted the foreign key as DROP To and CREATE To, then ran the DROP To script. I assume that running the CREATE To script will set everything back to what it was, as if it had never been dropped.
Thanks.
quote:Originally posted by visakh16
table B had a on delete cascade specified on foreign key constraint i think
you need to drop and recreate the fk without cascading option for avoiding this
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Yes, that turned out to be the magical secret. I scripted the foreign key as DROP To and CREATE To, then ran the DROP To script. I assume that running the CREATE To script will set everything back to what it was, as if it had never been dropped.
Thanks.
quote:Originally posted by visakh16
table B had a on delete cascade specified on foreign key constraint i think
you need to drop and recreate the fk without cascading option for avoiding this
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
make sure you remove ON DELETE clause before you create it again
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
If the second table had a constraint specified with an ON DELETE CASCADE then this *was the desired behaviour*
If you change this you won't be able to delete from tableA before removing the relevant rows in tableB (because to do so would invalidate the key constraint....)
So are you sure you actually have a problem here?
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Case of the Mysterious Missing Trigger
Posted - 07/27/2012 : 12:02:28
