Foreign Key constraint

Question

Hi

Two table are tied with each other because of FK constraint. I am trying to update these tables by disabling ALL Trigger but still getting the following error :-

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_TEST_REFERRING_REFPHYSI". The conflict occurred in database "ccdb", table "dbo.RefPhysician", column 'RefID'. The statement has been terminated."

Thats how I am trying to accomplice 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 = '[email protected]' 
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

A TRIGGER is has nothing to do with your Foreign Key constraint in this instance so you can completely remove all references to enabling and disabling TRIGGERS.

ALTER TABLE [Test] DROP CONSTRAINT [FK_TEST_REFERRING_REFPHYSI]

Your UPDATE Statements

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

REFERENCES [RefPhysician] ([RefID])

Answer 2

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

In general using ddl as a part of data update routine is really bad - you should do this sort of things

Answer 3

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 in the last two rows is to ensure that SQL Server trusts the FK constraints again after re-enabling. You really don't want to re-enable them as untrusted!

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

Answer 4

Triggers and Key Constraints are different things and, although they can sometimes be used to create the same effect, disabling one 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 simply in order to get data into the system that isn't allowed by the system design

If you are attempting to change a primary key in one table that is a foreign key in another, the appropriate approach is to CASCADE the change from one table to another, which can be done declaratively (that is, it will happen automatically when you update the primary key). Alternatively, you can obtain the PK value of the record in the dependent table, NULL out the FK field (if allowed by the schema), update the controlling record, then update the FK value in the