Postgres：添加约束（如果尚不存在） [英] Postgres: Add constraint if it doesn't already exist

问题描述

Postgres有什么办法说 ALTER TABLE foo ADD CONSTRAINT bar ... 如果约束已经存在，它将忽略该命令，因此它不会

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?

推荐答案

这可能有帮助，尽管可能有点脏：

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'

然后调用：

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 ...;

在您的开发数据库中可能还不错，或者您知道可以关闭依赖于此的应用程序该数据库用于维护窗口。

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.

但是，如果这是一个关键任务的24x7全天候生产环境，那么您真的不想像这样随意地放弃约束。即使是几毫秒，也会有一小段窗口，您不再需要执行约束，这可能会导致错误值漏掉。这可能会带来意想不到的后果，从而在将来的某个时候导致可观的业务成本。

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.

这篇关于Postgres：添加约束（如果尚不存在）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！