5

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?

user582485
  • 509
  • 3
  • 13
  • 23
  • 1
    Why are you needing to update your PK? Seems like something's not being done properly here. – Andrew Barber Jan 30 '12 at 04:47
  • Check this post: http://stackoverflow.com/questions/799100/changing-a-record-in-a-table-sql-server-that-has-foreign-keys – NoChance Jan 30 '12 at 04:56
  • In presence of cascade update rule, any changes/updates to primary key gets reflected to the foreign key. You do not have to disable the foreign key constraint in that case. – Anand Patel Jan 30 '12 at 05:45

2 Answers2

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.
Community
  • 1
  • 1
sisdog
  • 2,649
  • 2
  • 29
  • 49
0

Try these steps:

  • Disable FK constraints temporarily (ALTER TABLE tableA WITH NOCHECK CONSTRAINT ALL).
  • Update your Primary Keys
  • Update your Foreign Keys to match the Primary Keys change
  • Enable back enforcing FK constraints
Gustavo F
  • 2,071
  • 13
  • 23