postgresql 仅当外键不存在时如何创建外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12855631/
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
How to create foreign key only if it doesn't exists already?
提问by Arthur Kalimullin
I'm using PostgreSQL 9.1.
我正在使用 PostgreSQL 9.1。
I have a table common.client_contactwhere I created foreign key using this code:
我有一个表common.client_contact，我在其中使用以下代码创建了外键：
ALTER TABLE common.client_contact
ADD FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);
If I execute this code, I will get several foreign keys with different names (like client_contact_contact_id_fkey1, client_contact_contact_id_fkey2, client_contact_contact_id_fkey3and so on).
如果我执行这个代码，我会用不同的名称（如一些外国键client_contact_contact_id_fkey1，client_contact_contact_id_fkey2，client_contact_contact_id_fkey3等）。
So, before creating new constraint, I need to check if it's exists.
因此，在创建新约束之前，我需要检查它是否存在。
I check if this constraint exists in pg_constrainttable:
我检查pg_constraint表中是否存在此约束：
SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey'
And now I need to combine them together. Something like
现在我需要将它们组合在一起。就像是
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)
or just
要不就
ALTER TABLE common.client_contact
ADD FOREIGN KEY IF NOT EXISTS (contact_id) REFERENCES common.contact_item(id)
But these two queries are produce syntax error. So, how can I do it in PostgreSQL?
但是这两个查询都会产生语法错误。那么，我怎样才能在 PostgreSQL 中做到这一点呢？
回答by Craig Ringer
Use a DOblock to execute it in PL/PgSQL.
使用DO块在 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;
$$;
You seem to be relying on the default constraint name generation, which isn't ideal. It's probably safer to use information_schemato check for the presence of a constraint that links the two columns.
您似乎依赖于默认的约束名称生成，这并不理想。使用information_schema检查链接两列的约束是否存在可能更安全。
The following query checks for a foreign key between the two tables without relying on the generated constraint name:
以下查询在不依赖生成的约束名称的情况下检查两个表之间的外键：
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';
回答by dkrebbers
one way around the issue you are having is to delete the constraint before you create it
解决您遇到的问题的一种方法是在创建约束之前删除它
ALTER TABLE common.client_contact DROP CONSTRAINT IF EXISTS client_contact_contact_id_fkey;
ALTER TABLE common.client_contact DROP CONSTRAINT IF EXISTS 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)
更改表 common.client_contact 添加约束 client_contact_contact_id_fkey 外键 (contact_id) 参考 common.contact_item(id)
The adding of the named constraint will pass.
添加命名约束将通过。
回答by Elkas
I don't know for sure if this will work but you can try it.
我不确定这是否有效，但您可以尝试一下。
Fires, before you do the query
在您执行查询之前触发
SET FOREIGN_KEY_CHECKS=0
Then you make the query and
然后你进行查询和
SET FOREIGN_KEY_CHECKS=1
