SQL Server: Deleting Rows with Foreign Key Constraints: Can Transactions override the constraints?

Question

I have a few tables where Foreign Key constraints are added. These are used with code generation to set up specific joins in generated stored procedures.

Is it possible to override these constraints by calling multiple deletes within a transaction, specifically "TransactionScope" in C# or is cascaded deleting absolutely required?

Answer 1

Do not use cascade delete, you can cause serious performance issues that way. The best procedure is to do the deletes in order from the lowest child table up to the parent table.

Disabling the foreign keys is a prescription for having data integrity problems. The only time something like that should be done is by a DBA who is extremely experienced and well aware of the issues that could cause. If you are asking this question, you are not yet experienced enough to use that technique. Remember when you disable the FK, you disable it for everyone not just your process.

Answer 2

The only way to "override" a foreign key constraint is to disable it:

Disabling a FOREIGN KEY constraint enables data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.

You need to use the ALTER TABLE command to disable a constraint, using the NOCHECK keyword. IE:

ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;

The only other alternative is to drop the constraint, and re-add when necessary.

The necessity of doing this should lead to discussions about how to model the tables so this is not necessary.