CODEWITHSUNDEEP
sqloracle-databaseforeign-keysconstraints
reiley
reiley

Reputation: 3761

Alter existing foreign constraint to new table

In my DB, I've a table:

USER
----
Id      Number
Name    Varchar
SSN     Number

For some reason we wanted to alter SSN to Varchar. Since it was not empty, so we created new table instead of altering the existing one:

USER2
-----
Id      Number
Name    Varchar
SSN     Varchar

Copied all the data from USER to USER2.

Then renamed original USER to USER_BACKUP.

Renamed USER2 to USER.

I know it was a bad approach but unfortunately it's done & in use.

As of today new USER table is ahead of USER_BACKUP as some data has now been added & deleted from it.

Now there are 2 existing table ADDRESS and USER_ADDRESS.

USER_ADDRESS stores relational data between USER & ADDRESS using User's Id(Foreign_Key) & Address's Id(Foreign_Key).

USER_ADDRESS
------------
Id          Number
User_id     Number
Address_Id  Number

But since original USER has been renamed, the foreign constraint has also been renamed and does not point to new USER, but it points to USER_BACKUP, which is not I wanted.

Data in USER_BACKUP is irrelevant to me now.

What steps should I follow to point USER_ADDRESS to new USER table, without affecting the data in both tables.

Please suggest.

P.S. - We are using Oracle 18C.

Upvotes: 0

Views: 430

Answers (1)

MT0
MT0

Reputation: 168751

Add the new constraint and drop the old constraint.

ALTER TABLE user_address ADD CONSTRAINT user_address__id__fk
  FOREIGN KEY (user_id) REFERENCES "USER" ( id );
ALTER TABLE user_address DROP CONSTRAINT name_of_previous_fk_to_user_backup;

If the data in the two tables is still similar enough then it will work.

If you have deleted items from the new USER table that are still in USER_BACKUP and referenced from the USER_ADDRESS table then it will fail and you will need to reconcile the differences between the tables.

db<>fiddle here

Upvotes: 1

Related Questions