Re: Constraints primary and foreign key

Camilla -

Ooops - you should never have to update a primary key (3NF and all that)!!

Anyway the steps I suggest are:

1. to disable (or drop) your foreign table constraints
2. update the child table rows with new primary key values from their old values

e.g.

	UPDATE table_child tc
 	SET	  tc.foreign_key = '&&new_value'
	WHERE  tc.foreign_key = '&&old_value';

3. update the parent table with the new primary key value from the old value (note: I hope you're not updating the parent table key to a pre-existing key or there could be real problems - check first. . .)

4. re-enable (re-create) your foreign key constraints.

If all's well you won't get errors when you create the constraint.

Note: Spool all your SQL DML statement runs to a log file so that if it all goes pear shaped at least you'll beable to reverse engineer everything back to like it was!

Good luck! If you need further assistance feel free to email me at work (see below) tomorrow.

Cheers.

(The initial comment about not updating primary keys was serious - perhaps you need to take a serious look at the database design.)

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com

These views are my own and not those of my employer. Normal conditions apply.

Camilla Straubel <Camilla.Straubel_at_gbg.frontec.se> wrote in article <334E7AE8.3BC8_at_gbg.frontec.se>...
> Hey!
>
> I have a problem.
> I have to update a primray key in a table. Another table has a foreign
> key to this table. What kind of statements do I use for this?
>
> Hoping for a fast answeer.
>
> /Camilla
>
Received on Wed Apr 16 1997 - 00:00:00 CEST