CODEWITHSUNDEEP
sqlsql-serverdatabase
Stephen Oberauer
Stephen Oberauer

Reputation: 5395

SQL Server : updating foreign key referential integrity

I have the following tables in a SQL Server database: A, B & C

I want to update B and C's A_ID to a different record, e.g.

UPDATE B SET A_ID = 2 WHERE ID = 1
UPDATE C SET A_ID = 2 WHERE B_ID = 1

Unfortunately due to referential integrity, I won't be able to update B.A_ID. If I could, C would have an invalid foreign key.

SQL Fiddle here

I suppose one way to do this would be to delete the foreign key constraint in C and recreate it after the query has run. Is there a way to do this without altering the table structure?

Upvotes: 1

Views: 2559

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82524

Unless I'm missing something, why not just use a transaction?

Since A_Id in both tables points to the Id column in A table, I see no problem to update each table's records in 2 different update statements wrapped in a single transaction. This way, if one of the updates fails, the transaction is rolled back:

BEGIN TRY
BEGIN TRANSACTION

UPDATE b 
SET a_id = 3 
WHERE id = 3;

UPDATE c
SET a_id = 3
WHERE b_Id = 3;

COMMIT TRANSACTION

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

The foreign key constraints should not pose a problem.
see fiddle here.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

When you want to be able to change the PK of a table that is referenced by a FK, you can, as you mentioned delete and re-create the FK.

Or you can re-create the FK with ON UPDATE CASCADE.

This means that if the PK referenced by the FK changes, then the FK will also automatically change.

EDIT: If you mean can you do this without any dropping or altering of the FK at all, then as Rich Brenner suggested in a comment, you can do the "update" in two (four, really) stages, first creating the data you want, and then deleting the data you no longer want:

  1. Add new desired data to B
  2. Add new data to C, referencing new data in B
  3. Delete undesired data from C
  4. Delete undesired data from B

Upvotes: 3

Related Questions