how to update a primary key if it is also the foreign key to another table?

Question

I have a table, tableA, which has a column myID. myID is a primary key in tableA and foreign key to tableB.

when i tried to update a particular record's myID in tableA:

update tableA
set myID = 123456
where myID= 999999

i got this error:

The UPDATE statement conflicted with the FOREIGN KEY constraint "tableA_FK00". The conflict occurred in database "mydatabase" , table "tableA" , column 'myID'.

i had set myID's Update Rule to 'Cascade' and Enforce Foreign Key Constraint to 'No' but i still cannot update. how should i proceed?

Answer 1

If there's a record in tableB that references tableA with PK 123456 and tableB is the table with the "tableA_FK00" constraint then you are violating the constraint. If you must change the PK of a row in tableA (and I'm not sure why you're doing that, PK's should never change!!!) you have the burden of making sure no other records reference it with FK constraints.

So if you insist on changing the PK in tableA:

1. Find which table has the constraint "tableA_FK00" (ensure it's only tableB see post here).
2. Temporarily remove the constraint in tableB
3. Update tableA
4. Update all rows in TableB that need their FK changed
5. Reapply FK constraint on tableB

Another option:

1. Insert all ids of rows from tableB with FK 123456 into a temp table (this table just has the keys of PK's from tableB)
2. Set tableB FK field to allows nulls
3. Set all fields in tableB FK = null by joining with the temp table on tableB PK
4. Change the row in tableA
5. Set all the rows in tableB to 999999 by joining with the temp table.