Oracle manually add an FK constraint

Question

Alright, since a client wants to automate a certain process, which includes creating a new key structure in a LIVE database, I need to create relations between tables.columns. Now I've found the tables ALL_CONS_COLS en USER_CONSTRAINTS to hold information about constraints. If I were to manually create constraints, by inserting into these tables, I should be able to recreate the original constraints. My question: are there any more tables I should look into? Do you have an alternate suggestions, as this sounds VERY dirty and error prone to begin with.

Current modus operandi:

• Create a new column in each table for the PK;

• Generate a guid for this PK;

• Create a new column in each table for the FKs;

• Fetch the guid associated with the
  FK;

....... done sofar......

• Add new constraint based on the old
  one;

• Remove old constraint;

• Rename new columns;

This is kind of dodgy and I'd rather change my method, any ideas would be helpful.

To put it different, client wants to change key structure from int to guid on a live database. What's the best way to approach this

Answer 1

First of all, you don't create/modify/drop constraints by messing with the data dictionary, but with the regular ALTER TABLE t ADD/MODIFY/DROP CONSTRAINT syntax.

In your scenario, I think you should use the following scenario:

1) Make sure no-one modifies data while this operation is going on

2) Drop the old foreign key constraints first to avoid name clashing with the new constraints.

3) Drop the old primary key constraints

4) Create the new primary key constraints on the guid columns

5) Create the new foreign key constraints

And then you are done.

Regards, Rob.