postgresql - 仅当外键不存在时如何创建外键？

Question

我正在使用 PostgreSQL 9.1。

我有一个表common.client_contact，我在其中使用以下代码创建了外键:

ALTER TABLE common.client_contact 
ADD FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);

如果我执行这段代码，我会得到几个不同名称的外键(比如 client_contact_contact_id_fkey1、client_contact_contact_id_fkey2、client_contact_contact_id_fkey3 等等) .

因此，在创建新约束之前，我需要检查它是否存在。

我检查此约束是否存在于 pg_constraint 表中:

SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey'

现在我需要将它们组合在一起。有点像

IF NOT EXISTS
    (SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey')
ALTER TABLE common.client_contact
    ADD CONSTRAINT client_contact_contact_id_fkey
    FOREIGN KEY (contact_id) REFERENCES common.contact_item(id)

或者只是

ALTER TABLE common.client_contact 
ADD FOREIGN KEY IF NOT EXISTS (contact_id) REFERENCES common.contact_item(id)

但这两个查询会产生语法错误。那么，我该如何在 PostgreSQL 中实现呢？

Answer 1

使用 DO block 在 PL/PgSQL 中执行它。

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey') THEN
        ALTER TABLE common.client_contact
            ADD CONSTRAINT client_contact_contact_id_fkey
            FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);
    END IF;
END;
$$;

您似乎依赖于默认的约束名称生成，这并不理想。使用 information_schema 检查是否存在链接两列的约束可能更安全。

以下查询在不依赖生成的约束名称的情况下检查两个表之间的外键:

SELECT 1
FROM information_schema.table_constraints tc 
INNER JOIN information_schema.constraint_column_usage ccu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
INNER JOIN information_schema.key_column_usage kcu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
WHERE constraint_type = 'FOREIGN KEY' 
  AND ccu.table_name = 'contact_item' 
  AND ccu.table_schema = 'common'  
  AND ccu.column_name = 'contact_id' 
  AND tc.table_schema = 'common' 
  AND tc.table_name = 'client_contact'
  AND kcu.column_name = 'id';