如何在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.