Suppose I have the following tables
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)
);
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);
Should succeed but
insert into matrix_params values (2,3);
Should fail.
A simple constraint for matrix_params does not work as it has no way of knowing what the corresponding type is in the plugins table.
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;
and wrap that in a CHECK constraint:
alter table matrix_params add constraint chk_is_matrix check (is_matrix(pluginid));
Then:
=> 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"
And the FK takes care of referential integrity and cascades.
Use a compound key in the referenced table and a CHECK
constraint in the referencing table e.g.
CREATE TABLE plugins (
id int primary key,
type text,
UNIQUE (type, id)
);
CREATE TABLE matrix_params (
id int primary key,
plugintype text DEFAULT 'matrix' NOT NULL
CHECK (plugintype = 'matrix'),
pluginid int NOT NULL,
FOREIGN KEY (plugintype, pluginid)
references plugins (type, id)
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With