0

Let's say I have a table with 3 columns

table : table 1 rec_no --> primary key ( integer ) product prodlocation

I'll try to do a record shift

update table1 set rec_no = rec_no - 1 --> works fine

update table1 set rec_no = rec_no + 1 --> failed, primary key violation

I understand that we should not manipulate the data if it is part of the primary key.

For discussion sake, any idea why the second query failed ? I think it is acceptable if both queries failed.

HansUp
  • 95,961
  • 11
  • 77
  • 135
user3015739
  • 635
  • 2
  • 8
  • 20
  • Primary key violations usually means that the value you are trying to set already exists in another record. However, did you run those two queries back to back? – Dan Bracuk Jan 15 '14 at 00:05
  • both queries are seperate function. I just run one query at a time. my data for rec_no are just numbers from 0 to 10. whether it is +1 or -1 the data will still exist. if both queries failed, it will be easier. only the -1 got success, that puzzled me. – user3015739 Jan 15 '14 at 00:15

1 Answers1

0

ON UPDATE CASCADE

You need to set ON UPDATE CASCADE for those foreign keys:

ALTER TABLE bar
ADD CONSTRAINT FK_foo_bar
FOREIGN KEY (fooid) REFERENCES foo(id)
ON UPDATE CASCADE

Then you simply update the FKs and referring fields will also be updated as part of the transaction:

UPDATE foo SET id = id + 1000

Note that to alter constraints they need to be dropped.

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115