SQL 仅当主键不存在时才将主键添加到 PostgreSQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9906656/
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
Add primary key to PostgreSQL table only if it does not exist
提问by Pavel S.
I have simple table creating script in Postgres 9.1. I need it to create the table with 2-attributes PK only if it does not exist.
我在 Postgres 9.1 中有简单的表创建脚本。仅当它不存在时，我才需要它来创建具有 2 个属性 PK 的表。
CREATE TABLE IF NOT EXISTS "mail_app_recipients"
(
"id_draft" Integer NOT NULL,
"id_person" Integer NOT NULL
) WITH (OIDS=FALSE); -- this is OK
ALTER TABLE "mail_app_recipients" ADD PRIMARY KEY IF NOT EXISTS ("id_draft","id_person");
-- this is problem since "IF NOT EXISTS" is not allowed.
Any solution how to solve this problem? Thanks in advance.
任何解决方案如何解决这个问题？提前致谢。
采纳答案by a_horse_with_no_name
Why not include the PK definition inside the CREATE TABLE:
为什么不在 CREATE TABLE 中包含 PK 定义：
CREATE TABLE IF NOT EXISTS mail_app_recipients
(
id_draft Integer NOT NULL,
id_person Integer NOT NULL,
constraint pk_mail_app_recipients primary key (id_draft, id_person)
)
回答by Tom Gerken
You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests.
您可以执行以下操作，但最好按照 a_horse_with_no_name 的建议将其包含在创建表中。
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE table_name
ADD PRIMARY KEY (id);
end if;
回答by Jakub Kukul
You can try to DROPit before creating it (DROPhas the IF EXISTSclause):
您可以DROP在创建它之前尝试它（DROP有IF EXISTS子句）：
ALTER TABLE mail_app_recipients DROP CONSTRAINT IF EXISTS mail_app_recipients_pkey;
ALTER TABLE mail_app_recipients ADD CONSTRAINT mail_app_recipients_pkey PRIMARY KEY ("id_draft","id_person");
Note that this require that you give a name to the primary key constraint - in this example mail_app_recipients_pkey.
请注意，这要求您为主键约束命名——在本例中mail_app_recipients_pkey。
