Foreign key constraint

Question

The two tables are related to each other due to the FK constraint. I am trying to update these tables by disabling ALL Trigger, but still getting the following error: -

The UPDATE operation contradicted the FOREIGN KEY constraint "FK_TEST_REFERRING_REFPHYSI". The conflict occurred in the database "ccdb", in the table "dbo.RefPhysician", in the column "RefID". Application completed.

This is how I try to complete my task. Please help or update the following T-SQL: -

Begin Transaction Begin Try ALTER TABLE Test DISABLE Trigger ALL ALTER TABLE RefPhysician DISABLE Trigger ALL UPDATE Test SET RefID = '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' WHERE RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45'; UPDATE RefPhysician SET RefID = '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' , SpecID = NULL , RefLastName = '117002 DR. BRAD DIBBLE' , RefFirstName = '201-190 CUNDLES RD E, BARRIE ONT L4M 4S5' , RefMiddleName = NULL , RefPhone1 = '6138365083' , RefPhone2 = 'print,read,866,1' , RefFax = '6476476464' , RefEmail = ' [e-mail] ' WHERE RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45' ALTER TABLE Test ENABLE Trigger ALL ALTER TABLE RefPhysician ENABLE Trigger ALL Commit Transaction End Try Begin Catch Rollback Transaction End Catch 

Answer 1

 ALTER TABLE Test NOCHECK CONSTRAINT ALL ALTER TABLE RefPhysician NOCHECK CONSTRAINT ALL ALTER TABLE Test WITH CHECK CHECK CONSTRAINT ALL ALTER TABLE RefPhysician WITH CHECK CHECK CONSTRAINT ALL 

IMPORTANT: The funny "WITH CHECK CHECK" syntax on the last two lines is to ensure that SQL Server returns FK constraints again after re-enabling. You really don't want to re-enable them as untrustworthy!

However, if you have any influence on the table design, I strongly suggest that mutable values ​​will not be used in primary or foreign keys. FK constraints work much better if they are based on internal identifiers that never change after insertion.

Answer 2

TRIGGER has nothing to do with your foreign key constraint in this case, so you can completely remove all references to turning TRIGGERS on and off.

 ALTER TABLE [Test] DROP CONSTRAINT [FK_TEST_REFERRING_REFPHYSI] 

Your UPDATE statements

 ALTER TABLE [Test] ADD CONSTRAINT [FK_TEST_REFERRING_REFPHYSI] FOREIGN KEY([RefID]) 

LINKS [RefPhysician] ([RefID])

Answer 3

If your update violates referential integrity, it will be rejected - disabling triggers will not help. Triggers have nothing to do with RI

In general, using ddl as part of the data update procedure is really bad - you should do such things

Answer 4

Triggers and key constraints are two different things, and although they can sometimes be used to create the same effect, disabling one of them is not the same as disabling the other.

A foreign key constraint is placed there to indicate that certain actions should not be allowed. You should never (almost never) drop them just to get data into the system that is not allowed by the system design

If you are trying to change the primary key in one table, which is a foreign key in another, then a suitable approach is to CASCADE change from one table to another, which can be done declaratively (that is, this will happen automatically when the primary key is updated). Alternatively, you can get the PK value of the record in the dependent table, NULL outside the FK field (if allowed by the schema), update the control record, and then update the FK value in