SQL Server: deleting rows with foreign key constraints: can transactions override constraints?

Question

I have several tables in which foreign key constraints are added. They are used with code generation to configure specific joins in the generated stored procedures.

Is it possible to override these restrictions by causing several deletions within a transaction, in particular, "TransactionScope" in C # or by completely cascading the deletion?

Answer 1

Do not use cascading deletion; you can cause serious performance problems this way. The best procedure is to do the deletions in order from the youngest child table to the parent table.

Disabling foreign keys is a recipe for data integrity issues. The only time this needs to be done is to have a DBA that is very experienced and knowledgeable about the problems that may arise. If you ask this question, you are not yet experienced enough to use this technique. Remember, when you disable 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 allows you to modify data in a table without checking constraints. Disable the FOREIGN KEY constraint during INSERT and UPDATE statements if new data violates the constraint or if the constraint should only apply to data already in the database.

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

 ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap; 

The only alternative is to waive the restriction and re-add if necessary.

The need for this should lead to a discussion of how to model tables so that this is not necessary.

Answer 3

You cannot override FK restrictions if you could decide what is the point of creating them in the first place?

Answer 4

If your FK restrictions are specifically set for use in stored procedures, it is not FK, is it? a good solution would be to update the appropriate code by creating constraints at the beginning of the proc and clearing them when your code is executed. Do not forget to deal with the case when your time limit cannot be checked for data.

Answer 5

Restrictions can be set either immediately or postponed until the end of the transaction. Delaying until the end of the transaction allows you to violate the restrictions when creating a transaction, but enforce them at the end of the transaction. As far as I understand, a delay until the end of the transaction is what you are likely to be after.