Postgres: 添加不存在的约束

Postgres 有没有什么方法可以说 ALTER TABLE foo ADD CONSTRAINT bar ...，如果约束已经存在，它会忽略命令，这样就不会产生错误？

118195 次浏览
小开

您可以在 pg_constraint表上运行查询来查找约束是否存在，或者不存在:

SELECT 1 FROM pg_constraint WHERE conname = 'constraint_name'"
小开
最佳答案

这可能会有所帮助，尽管这可能有点肮脏:

create or replace function create_constraint_if_not_exists (
t_name text, c_name text, constraint_sql text
)
returns void AS
$$
begin
-- Look for our constraint
if not exists (select constraint_name
from information_schema.constraint_column_usage
where table_name = t_name  and constraint_name = c_name) then
execute constraint_sql;
end if;
end;
$$ language 'plpgsql'

然后打电话给:

SELECT create_constraint_if_not_exists(
'foo',
'bar',
'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')

更新:

根据 Webmut 的回答的建议:

ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;

在您的开发数据库中，或者在您知道可以将依赖于该数据库的应用程序关闭以获得一个维护窗口的地方，这样做可能没有问题。

但是，如果这是一个生动的任务关键的24x7生产环境，你不会真的想要放弃约束，任意像这样。即使只有几毫秒，也有一个很短的时间窗口，您不再强制执行约束，这可能会允许错误的值溜走。在未来某个时候，这种意外后果可能会导致相当大的商业成本。

小开

不知道为什么这么多行代码？

从 dbo 中选择“ Column1”，“ Column2”，“ Column3”，count (star) FROM dbo

如果存在“ MyConstraint _ Name”，则删除约束;

ALTER TABLE dbo."MyTable" ADD CONSTRAINT "MyConstraint_Name" UNIQUE("Column1", "Column3", "Column2");

小开

A possible solution is to simply use DROP IF EXISTS before creating the new constraint.

ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;

似乎比尝试查询 information _ schema 或目录更容易，但是对于大型表可能会比较慢，因为它总是重新创建约束。

编辑2015-07-13: Kev 在 他的回答中指出，我的解决方案在约束不存在且没有强制执行时创建一个短窗口。尽管如此，但是通过在事务中包装这两个语句，可以很容易地避免这样的窗口。

小开

可以在匿名 DO 块中使用异常处理程序来捕获重复的对象错误。

DO $$
BEGIN


BEGIN
ALTER TABLE foo ADD CONSTRAINT bar ... ;
EXCEPTION
WHEN duplicate_table THEN  -- postgres raises duplicate_table at surprising times. Ex.: for UNIQUE constraints.
WHEN duplicate_object THEN
RAISE NOTICE 'Table constraint foo.bar already exists';
END;


END $$;

Http://www.postgresql.org/docs/9.4/static/sql-do.html http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html

小开

在包含大量数据的表上创建约束可能是一个代价高昂的操作，因此我建议不要删除约束，而是在创建之后立即重新创建约束——您只想创建一次约束。

我选择使用一个匿名代码块来解决这个问题，这与 Mike Stankavich 非常相似，但与 Mike 不同(他发现了一个错误) ，我首先检查约束是否存在:

DO $$
BEGIN
IF NOT EXISTS ( SELECT  constraint_schema
,       constraint_name
FROM    information_schema.check_constraints
WHERE   constraint_schema = 'myschema'
AND   constraint_name = 'myconstraintname'
)
THEN
ALTER TABLE myschema.mytable ADD CONSTRAINT myconstraintname CHECK (column <= 100);
END IF;
END$$;
小开

考虑到上面提到的所有答案，如果您只是想检查您试图插入的表中是否存在约束，并且如果恰好有约束，则下面的方法将提供帮助

DO
$$ BEGIN
IF NOT EXISTS (select constraint_name
from information_schema.table_constraints
where table_schema='schame_name' and upper(table_name) =
upper('table_name')  and upper(constraint_name) = upper('constraint_name'))


THEN


ALTER TABLE TABLE_NAME ADD CONSTRAINT CONTRAINT_NAME..... ;


ELSE raise NOTICE 'Constraint CONTRAINT_NAME already exists in Table TABLE_NAME';


END IF;
END
$$;
小开

在 psql 中，可以使用元命令 gexec 运行生成的查询。

SELECT 'ALTER TABLE xx ADD CONSTRAINT abc' WHERE not EXISTS (SELECT True FROM pg_constraint WHERE conname = 'abc') \gexec

小开

使用 Information _ schema检查约束对外键不起作用。我使用 pg_constraint检查 主键、 外国钥匙或 独一无二约束:

CREATE OR REPLACE FUNCTION add_constraint(t_name text, c_name text, constraint_sql text)
RETURNS void
AS $$
BEGIN
IF NOT EXISTS(
SELECT c.conname
FROM pg_constraint AS c
INNER JOIN pg_class AS t ON c.conrelid = t."oid"
WHERE t.relname = t_name AND c.conname = c_name
) THEN


EXECUTE 'ALTER TABLE ' || t_name || ' ADD CONSTRAINT ' || c_name || ' ' || constraint_sql;


END IF;
END;
$$
LANGUAGE plpgsql;

例子:

SELECT add_constraint('client_grant_system_scopes', 'client_grant_system_scopes_pk', 'PRIMARY KEY (client_grants_id, tenant, "scope");');


SELECT add_constraint('client_grant_system_scopes', 'client_grant_system_scopes_fk', 'FOREIGN KEY (tenant,"scope") REFERENCES system_scope(tenant,"scope") ON DELETE CASCADE;');


SELECT add_constraint('jwt_assertion_issuers', 'jwt_assertion_issuers_issuer_key', 'UNIQUE (issuer);');
小开

利用 regclass减少冗余，提高性能，避免与模式之间的表命名冲突相关的错误:

DO $$ BEGIN
IF NOT EXISTS (SELECT FROM pg_constraint
WHERE conrelid = 'foo'::regclass AND conname = 'bar') THEN
ALTER TABLE foo ADD CONSTRAINT bar...;
END IF;
END $$;

This will also work for tables in other schemas, e.g.:

DO $$ BEGIN
IF NOT EXISTS (SELECT FROM pg_constraint
WHERE conrelid = 's.foo'::regclass AND conname = 'bar') THEN
ALTER TABLE s.foo ADD CONSTRAINT bar...;
END IF;
END $$;
小开

对我来说，这些解决方案不起作用，因为约束是主键。 这个对我很管用:

ALTER TABLE <table.name> DROP CONSTRAINT  IF EXISTS <constraint.name> CASCADE;

提交答案