如何在PostgreSQL中使用对引用表的约束创建外键 [英] How to make a foreign key with a constraint on the referenced table in PostgreSQL
本文介绍了如何在PostgreSQL中使用对引用表的约束创建外键的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有以下表格:
CREATE TABLE plugins (
id int primary key,
type text);
insert into plugins values (1,'matrix');
insert into plugins values (2,'matrix');
insert into plugins values (3,'function');
insert into plugins values (4,'function');
CREATE TABLE matrix_params (
id int primary key,
pluginid int references plugins (id)
);
这一切都按预期工作,但我想添加一个额外的约束,matrix_param只能具有类型matrix的pluginid。
This all works as expected but I would like to add an additional constraint that a matrix_param can only refer to the pluginid that has type 'matrix'. So
insert into matrix_params values (1,1);
应该成功,但
insert into matrix_params values (2,3);
应该失败。
推荐答案
因为matrix_params不能工作,因为它无法知道插件表中对应的类型。这是一个CHECK约束。您不能将查询放在CHECK约束中,但可以调用函数;所以,我们构建一个简单的函数,告诉我们 pluginid
是一个矩阵:
You can use a CHECK constraint for this. You can't put a query in a CHECK constraint but you can call a function; so, we build a simple function that tells us if a pluginid
is a matrix:
create or replace function is_matrix(int) returns boolean as $$
select exists (
select 1
from plugins
where id = $1
and type = 'matrix'
);
$$ language sql;
并在CHECK约束中换行:
and wrap that in a CHECK constraint:
alter table matrix_params add constraint chk_is_matrix check (is_matrix(pluginid));
然后:
=> insert into matrix_params values (1,1);
=> insert into matrix_params values (2,3);
ERROR: new row for relation "matrix_params" violates check constraint "chk_is_matrix"
FK负责引用完整性和级联。
And the FK takes care of referential integrity and cascades.
这篇关于如何在PostgreSQL中使用对引用表的约束创建外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文