SQL Postgres：如果尚不存在则添加约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6801919/
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
Postgres: Add constraint if it doesn't already exist
提问by Paul A Jungwirth
Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ...which will just ignore the command if the constraint already exists, so that it doesn't raise an error?
ALTER TABLE foo ADD CONSTRAINT bar ...如果约束已经存在，Postgres 有没有办法说哪个只会忽略命令，这样它就不会引发错误？
采纳答案by Kev
This might help, although it may be a bit of a dirty hack:
这可能会有所帮助，尽管它可能有点脏：
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'
Then call with:
然后调用：
SELECT create_constraint_if_not_exists(
'foo',
'bar',
'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')
Updated:
更新：
As per Webmut's answerbelow suggesting:
根据以下Webmut 的回答，建议：
ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;
That's probably fine in your development database, or where you know you can shut out the apps that depend on this database for a maintenance window.
这在您的开发数据库中可能没问题，或者您知道可以在维护窗口中关闭依赖此数据库的应用程序。
But if this is a lively mission critical 24x7 production environment you don't really want to be dropping constraints willy nilly like this. Even for a few milliseconds there's a short window where you're no longer enforcing your constraint which may allow errant values to slip through. That may have unintended consequences leading to considerable business costs at some point down the road.
但是，如果这是一个活跃的关键任务 24x7 生产环境，您真的不想像这样随意地放弃约束。即使只有几毫秒，也有一个短窗口，您不再强制执行您的约束，这可能会允许错误的值溜走。这可能会产生意想不到的后果，在未来的某个时刻导致可观的业务成本。
回答by Webmut
A possible solution is to simply use DROP IF EXISTS before creating the new constraint.
一个可能的解决方案是在创建新约束之前简单地使用 DROP IF EXISTS。
ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;
Seems easier than trying to query information_schema or catalogs, but might be slow on huge tables since it always recreates the constraint.
似乎比尝试查询 information_schema 或 catalogs 更容易，但在大型表上可能会很慢，因为它总是重新创建约束。
Edit 2015-07-13: Kev pointed out in his answerthat my solution creates a short window when the constraint doesn't exist and is not being enforced. While this is true, you can avoid such a window quite easily by wrapping both statements in a transaction.
编辑 2015-07-13：Kev 在他的回答中指出，当约束不存在且未强制执行时，我的解决方案会创建一个短窗口。虽然这是真的，但您可以通过将两个语句包装在一个事务中来很容易地避免出现这样的窗口。
回答by Mike Stankavich
You can use an exception handler inside an anonymous DO block to catch the duplicate object error.
您可以在匿名 DO 块中使用异常处理程序来捕获重复对象错误。
DO $$
BEGIN
BEGIN
ALTER TABLE foo ADD CONSTRAINT bar ... ;
EXCEPTION
WHEN duplicate_object THEN RAISE NOTICE 'Table constraint foo.bar already exists';
END;
END $$;
http://www.postgresql.org/docs/9.4/static/sql-do.htmlhttp://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.htmlhttp://www.postgresql.org/docs/9.4/static/errcodes-appendix.html
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
回答by jamiet
Creating constraints can be an expensive operation on a table containing lots of data so I recommend not dropping constraints only to immediately create them again immediately after - you only want to create that thing once.
在包含大量数据的表上创建约束可能是一项昂贵的操作，因此我建议不要删除约束只是为了立即再次创建它们 - 您只想创建一次。
I chose to solve this using an anonymous code block, very similar to Mike Stankavich, however unlike Mike (who catches an error) I first check to see if the constraint exists:
我选择使用匿名代码块来解决这个问题，与 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$$;
回答by Behnam
you can run query over pg_constrainttable to find constraint exists or not.like:
您可以对pg_constraint表运行查询以查找约束是否存在。例如：
SELECT 1 FROM pg_constraint WHERE conname = 'constraint_name'"
回答by charle819
Considering all the above mentioned answers , the below approach help if you just want to check if a constraint exist in the table in which you are trying to insert and raise a notice if there happens to be one
考虑到上述所有答案，如果您只想检查尝试插入的表中是否存在约束并在碰巧有约束时发出通知，则以下方法会有所帮助
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
$$;
回答by user3121618
Don't know why so many lines of code ?
不知道为什么这么多行代码？
-- SELECT "Column1", "Column2", "Column3" , count(star) FROM dbo."MyTable" GROUP BY "Column1" , "Column2" , "Column3" HAVING count(*) > 1;
-- SELECT "Column1", "Column2", "Column3" , count(star) FROM dbo."MyTable" GROUP BY "Column1" , "Column2" , "Column3" HAVING count(*) > 1;
alter table dbo."MyTable" drop constraint if exists "MyConstraint_Name" ;
如果存在，则更改表 dbo."MyTable" 删除约束 "MyConstraint_Name" ;
ALTER TABLE dbo."MyTable" ADD CONSTRAINT "MyConstraint_Name" UNIQUE("Column1", "Column3", "Column2");
ALTER TABLE dbo."MyTable" ADD CONSTRAINT "MyConstraint_Name" UNIQUE("Column1", "Column3", "Column2");
