如何重命名 Oracle 上的默认约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39619226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to rename a default constraint on Oracle?
提问by Chinovski
I want to rename many constraints (PK, FK, ...etc. ) that have default names which start with 'SYS' to be able to insert the same data in other DB.
我想重命名许多具有以“SYS”开头的默认名称的约束（PK、FK、...等），以便能够在其他数据库中插入相同的数据。
I found the following script that I changed to get what I want:
我找到了以下脚本，我对其进行了更改以获得我想要的：
BEGIN
FOR cn IN (
SELECT constraint_name
FROM user_constraints
WHERE constraint_type = 'P'
AND table_name = 'SPECIALITE'
)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || cn.table_name || ' RENAME CONSTRAINT ' || cn.constraint_name || ' TO PK_' || 'SPECIALITE';
END LOOP;
END;
This script works, but it seems a bit complicated for me, I wonder if it exists something like:
这个脚本有效，但对我来说似乎有点复杂，我想知道它是否存在类似的东西：
ALTER TABLE 'SPECIALITE' RENAME CONSTRANT (....)
The problem is I don't know the name of constraints, they have a default name, I know only tables where they are.
问题是我不知道约束的名称，它们有一个默认名称，我只知道它们所在的表。
Is it possible?
是否可以？
回答by mathguy
As you already know, you need to run two queries.
如您所知，您需要运行两个查询。
select constraint_name from user_constraints
where table_name = <table_name> and constraint_type = 'P';
and with the constraint name in hand,
和手头的约束名称，
alter table <table_name> rename constraint <old_constr_name> to <new_constr_name>;
This will require copying the constraint name from the first query and pasting it into the second, in the proper place (<old_constr_name>).
这将需要从第一个查询中复制约束名称并将其粘贴到第二个查询中的适当位置 ( <old_constr_name>)。
If this was all, I wouldn't post an answer. But I remember something I read on AskTom some time ago - a clever way to avoid copying and pasting, using the COLUMN command in SQL*Plus. (This may also work in SQL Developer and Toad.) Something like this:
如果这就是全部，我不会发布答案。但我记得我前段时间在 AskTom 上读到的一些内容 - 一种避免复制和粘贴的巧妙方法，使用 SQL*Plus 中的 COLUMN 命令。（这也可能适用于 SQL Developer 和 Toad。）像这样：
column constraint_name new_val c -- Note: no semicolon - this is SQL*Plus, not SQL
select constraint_name from user_constraints
where table_name = <table_name> and constraint_type = 'P';
alter table <table_name> rename constraint &c to <new_constr_name>;
If you need to change many PK constraint names, this will save some work. The constraint name returned by the SELECT query is saved in the "new_val" labeled "c" from the SQL*Plus COLUMN command, and it is used in the ALTER TABLE statement.
如果您需要更改许多 PK 约束名称，这将节省一些工作。SELECT 查询返回的约束名称保存在来自 SQL*Plus COLUMN 命令的标记为“c”的“new_val”中，并在 ALTER TABLE 语句中使用。
回答by Nico
If you don't have the constraint name, you can't do it directly. But you can easily generate the statement.
如果您没有约束名称，则无法直接执行。但是您可以轻松生成该语句。
select 'ALTER TABLE ' || table_name || ' RENAME CONSTRAINT ' || constraint_name || ' TO PK_' || upper( table_name )
from user_constraints
where constraint_type = 'P'
and constraint_name like 'SYS%';
This select will list all tables with a constraint on its primary key that start by 'SYS'and rename it to PK_TABLE_NAME;
此选择将列出所有在其主键上具有约束的表，这些表以“SYS”开头，并将其重命名为 PK_TABLE_NAME；
You just have to check if all statements looks ok for you and run them.
您只需要检查所有语句是否适合您并运行它们。
You also can use the generated column like this
您也可以像这样使用生成的列
select 'ALTER TABLE ' || table_name || ' RENAME CONSTRAINT ' || constraint_name || ' TO PK_' || upper( table_name )
from user_constraints
where constraint_type = 'P'
and generated = 'GENERATED NAME';
but you will have BIN tables and maybe other unwanted tables
但是你会有 BIN 表，也许还有其他不需要的表
回答by isalgueiro
Add user_cons_columnsview to your SQL so you can generate constraint names. Here is a quick and dirty example:
将user_cons_columns视图添加到您的 SQL，以便您可以生成约束名称。这是一个快速而肮脏的示例：
select 'ALTER TABLE ' || c.table_name || ' RENAME CONSTRAINT ' || c.constraint_name || ' TO ' || substr(c.constraint_type || '_' || c.table_name || '_' || replace(wm_concat(cc.column_name), ',', '_'), 0, 30) || ';'
from user_constraints c
join user_cons_columns cc on c.table_name = cc.table_name and c.constraint_name = cc.constraint_name
where c.constraint_name like 'SYS%'
group by c.table_name, c.constraint_name, c.constraint_type;
This SQL generates an executable script with commands like this:
此 SQL 使用如下命令生成可执行脚本：
ALTER TABLE TABLENAME RENAME CONSTRAINT SYS_xxxxxx TO C_TABLENAME_COLUMN;
Please note that I'm using undocumented wm_concatfunction. If you're using Oracle >= 11g consider using listagginstead.
请注意，我使用的是未记录的wm_concat功能。如果您使用 Oracle >= 11g，请考虑listagg改用。
