Changing primary key value in Oracle

Question

Is there a way to change the primary key value referenced by another table as a foreign key?

Answer 1

A simpler alternative is to insert a new line and delete the old one. (Update any link rows in other tables before deleting)

Answer 2

There is no built-in UPDATE CASCADE if that is what you need. You will need to do something like disabling any FK restrictions; Run UPDATE statements enable restrictions again.

Note that updating primary keys (usually always) is a bad idea.

Answer 3

, .

"update cascade", " Tom Kyte"

Answer 4

, ( , - ). : fooobar.com/questions/392269/...

update MY_TABLE t1
set t1.MY_KEY = (case t1.MY_KEY = 100 then 101 else 100 end)
where t1.MYKEY in (100, 101)

Answer 5

, Oracle, ( / ). . / .

- , . (, , Oracle 11.2 . 12.1, , .)

, , .

Answer 6

, constrainsts, udates constrainst. script, disable script: (, )

script SELECT 'alter table ' || uc.table_name|| ' disable constraint '|| uc.constraint_name|| ' ;' FROM user_constraints uc inner join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name where column_name = 'MYCOLUMN_USED_AS_FOREIGN_KEY' and constraint_type='R' / script

alter table MYTABLE1 disable constraint FK_MYTABLE1 ; alter table MYTABLE2 disable constraint MYTABLE2 ; alter table MYTABLE3 disable constraint FK3_MYTABLE3 ; ...

PK: update MYTABLE1 set MYFIELD= 'foo' where MYFIELD='bar'; update MYTABLE2 set MYFIELD= 'foo' where MYFIELD='bar'; update MYTABLE3 set MYFIELD= 'foo' where MYFIELD='bar'; commit; script:

SELECT 'alter table ' || uc.table_name|| ' enable constraint '|| uc.constraint_name|| ' ;' FROM user_constraints uc inner join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name where column_name = 'MYCOLUMN_USED_AS_FOREIGN_KEY' and constraint_type='R'

Answer 7

, , - , , .. Oracle, , -transaction.

, "alter table", , , ..

alter table <table name> drop constraint <FK constraint name>;
alter table <table name> add constraint <FK constraint name> foreign key .... initially deferrable;

, , - :

• FK , ;
• - FK - , .

, , Oracle , . , , , -, .

This is also a one-time change, so you don't need to run DDL every time you want to update primary keys.