0

I have following tables

tblA(id,name,address) pk:id

tblB(depname,depaddress,depmanager_id) pk:depname fk: depmanager_id reference tblA(id on update restrict)

they have following data:

tblA

id    name    address
1     jazz    kathmandu
2     hari    pokhara

tblB
depname    depaddress    depmanager
HR         Chicago        1
ECA        Paris          2

Now the situation is I want to update the id of record with id 1 in tblA with different id. How can i do it? (PS: The requirement states that I cannot alter the existing structure of table nor can I create any new tables)

Parag Rayamajhi
  • 1
  • 2
  • Can you not drop the FK constraint, make the necessary update on both tables then recreate the FK constraint? – Simon May 11 '17 at 02:58
  • This post might explain: [Why updating `PRIMARY KEY` is not a good design / idea](http://stackoverflow.com/a/3838649/2298301). If you want to have a field that would be updated intermittently, consider not having it as a `PRIMARY KEY`. On the other hand, you can always have [`ON UPDATE CASCADE`](http://stackoverflow.com/q/1481476/2298301) if you want to enforce the updates across all the `FOREIGN KEY`s upon a change in the value of the primary key. – Dhruv Saxena May 11 '17 at 03:02
  • I cannot. The requirement is that I cannot alter existing structure of table. – Parag Rayamajhi May 11 '17 at 03:03
  • Doing this defeats the purpose of a primary key. I think you need to talk to whomever is preventing you from changing the structure of the table, cause this sort of design decisions will bite you hard very soon. – Shadow May 11 '17 at 07:00

1 Answers1

0

You should create a new table (tblA1) and insert the data from tblA with new ids.

After that, remove the FK, update the second table (tblB) with the new ids (mapping tblA ids to tblA1 ids).

Once the ids are correctly mapped, you can set up a new FK between tblB and tblA1.

Gratus D.
  • 787
  • 7
  • 22