0

I know the impact of my question, but my case may be unique.

I am trying to create a delta script to apply to other test databases that change a foreign key's child table. For instance:

Table A has some_id with a foreign key pointing to a column in table B

I want to change some_id to point to a column in table C

I know this could harm and potentially ruin a system if there was already data there, but in my case there is no data in the parent table. I will simply be switching the foreign key to point to a different column of a different table.

Preferably, I would like a script to do this.

drix
  • 177
  • 3
  • 9
  • 1
    Checkout this [Change the foreign key referential action][1] may be this will help you. [1]: http://stackoverflow.com/questions/3359329/how-to-change-the-foreign-key-referential-action-behavior?rq=1 – Pulkit Agarwal May 15 '14 at 12:48

1 Answers1

0

Since you said, you already know the implications of doing this...

You just need to drop the Foreign Key constraint on your old table. Then create a new Foreign Key Constraint joining with your new table

Here is an example:

-- create test tables
create table A (column1 bigint primary key, column2 varchar(255))
create table B (column1 bigint primary key, column2 varchar(255), column3 bigint)
create table C (column1 bigint primary key, column2 varchar(255), column3 bigint)
alter table B add constraint fk_B_A foreign key (column3) references table1(column1)

-- change the constraints like this
alter table B drop constraint fk_B_A
alter table C add constraint fk_C_A foreign key (column3) references table1(column1)

-- drop test table
drop table A
drop table B
drop table C
Pradeep Kumar
  • 6,836
  • 4
  • 21
  • 47