在 Oracle 中更改主键值

Change primary key value in Oracle

提问by paweloque

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

有没有办法更改另一个表作为外键引用的主键的值？

回答by nvogel

An easier alternative is to insert a new row and delete the old one. (Update any referencing rows in other tables before you do the delete)

一种更简单的替代方法是插入新行并删除旧行。（在删除之前更新其他表中的任何引用行）

回答by cagcowboy

There isn't an in-built UPDATE CASCADE if that's what you're after. You'd need to do something like disable any FK constraints; run UPDATE statements; re-enable the constraints.

如果这就是您所追求的，则没有内置的 UPDATE CASCADE。你需要做一些事情，比如禁用任何 FK 约束；运行 UPDATE 语句；重新启用约束。

Note that updating Primary Keys is (usually always) a bad idea.

请注意，更新主键（通常总是）是一个坏主意。

回答by Tony Andrews

You will need to disable the foreign key constraints before changing the primary key values, and then re-enable them afterwards.

您需要在更改主键值之前禁用外键约束，然后再重新启用它们。

If you actually want to implement "update cascade" functionality instead then see Tom Kyte's Update Cascade package

如果您确实想实现“更新级联”功能，请参阅Tom Kyte 的更新级联包

回答by Bart Juriewicz

It is possible even without disabling constraints, in case if you would like only to swap keys (which is also a change's subset, so it might be still answer to your question). I wrote an example here: https://stackoverflow.com/a/26584576/1900739

如果您只想交换密钥（这也是更改的子集，因此它可能仍然是您问题的答案），即使不禁用约束也是可能的。我在这里写了一个例子：https: //stackoverflow.com/a/26584576/1900739

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

回答by nop77svk

Yes, there is a way to do the cascading update in Oracle, even within a transaction (which does not hold true for the option of enabling/disabling constraints). However, you'll have to implement it yourself. It can be done via before/after-row-update triggers.

是的，有一种方法可以在 Oracle 中进行级联更新，即使在事务中也是如此（这不适用于启用/禁用约束的选项）。但是，您必须自己实现它。它可以通过 before/after-row-update 触发器来完成。

It is possible due to the fact that triggers are executed before any constraints are checked. (Well, at least in Oracle 11.2 it was true. Haven't checked against 12.1, but I honestly believe it hasn't changed.)

由于在检查任何约束之前执行触发器，这是可能的。（好吧，至少在 Oracle 11.2 中确实如此。还没有针对 12.1 进行检查，但老实说我相信它没有改变。）

Anyway, as said before, updating primary keys is usually a bad idea.

无论如何，如前所述，更新主键通常是一个坏主意。

回答by error500

The principe is to disable constrainsts, run your udates based on key, and reenable the constrainst. That for here is a script that run the disable script : (Assuming all the constraints are enable at start)

原则是禁用约束，根据密钥运行您的日期，然后重新启用约束。这是一个运行禁用脚本的脚本：（假设所有约束都在开始时启用）

Generate the 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' Copy/paste the generated script and run it

生成脚本 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' 复制/粘贴生成的脚本并运行它

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

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

Then update your PK values : 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; Generate the enable constraints script :

然后更新您的 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; 生成启用约束脚本：

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'

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'

回答by Nathan Marston

Another way you can do this is by changing the foreign key constraints so that the validation of the constraint is deferred until you commit - i.e. instead of Oracle validating the constraints statement-by-statement, it'll do it transaction-by-transaction.

另一种方法是更改​​外键约束，以便将约束的验证推迟到您提交 - 即不是 Oracle 逐个语句验证约束，而是逐个事务地进行验证。

Note you can't do this via the "alter table" statement, but you can drop and re-create the foreign key constraint to be deferrable, i.e:

请注意，您不能通过“alter table”语句执行此操作，但您可以删除并重新创建可延迟的外键约束，即：

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

Once you've done that, just update the tables in whatever order you like, and commit - at which point, either:

完成后，只需按照您喜欢的任何顺序更新表，然后提交 - 此时，要么：

1. All your FK constraints are satisfied, and everyone's happy; or
2. You've violated a FK constraint somewhere - you'll get an error and you'll have to fix up the data and commit, or rollback.

1. 你所有的FK约束都满足了，大家都开心；或者
2. 你在某处违反了 FK 约束 - 你会得到一个错误，你必须修复数据并提交或回滚。

Note this feature is quite safe, as Oracle does not allow dirty reads so they'll only see the effects of your updates once you commit. So from the perspective of every other session, referential integrity appears to be preserved.

请注意，此功能非常安全，因为 Oracle 不允许脏读，因此他们只会在您提交后才能看到更新的效果。因此，从每个其他会话的角度来看，参照完整性似乎得到了保留。

Also, this is a once-off change, so you don't need to go executing DDL each time you want to go updating the primary keys.

此外，这是一次性更改，因此您无需在每次要更新主键时都执行 DDL。