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?
You can't override FK constrains, if you could what would be the point of creating them in the first place?
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.
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.
If your FK constraints are specifically set for specific use in stored procedures, these are not really FK's, aren't they? a nice solution would be to update the corresponding code by creating the constraints at the beginning of the proc, and clearing them when your code is done. Do not forget then to deal with the case where your temporary constraint cannot be checked against the data.