How to make a foreign key with a constraint on the referenced table in PostgreSQL

Question

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.

score 7 · Accepted Answer · answered Apr 13 '12 at 06:05

7

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.

answered Apr 13 '12 at 06:05

mu is too short

426,620
70
833
800

2

If type is updated after a matrix_params row references the plugins row, things won't be in the desired state; otherwise this works. – kgrittn Apr 13 '12 at 12:31
@kgrittn: You could add an UPDATE trigger on `plugins` (possibly with a `WHEN old.type = 'matrix' and new.type != old.type` condition if your PostgreSQL version supports it), that trigger could raise an exception if the `is_matrix` condition was violated by the update. That might be getting a bit ugly though. – mu is too short Apr 13 '12 at 18:36

score 3 · Answer 2 · answered Apr 13 '12 at 08:21

3

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)
);

answered Apr 13 '12 at 08:21

onedaywhen

55,269
12
100
138

This changes the primary key of one table to something other than specified in the question, and adds an unnecessary column to every row in the other. – kgrittn Apr 13 '12 at 12:33
@kgrittn: I haven't changed any primary key: I've added an additional superkey to the referenced table. The added column in the referencing table enables a row-level `CHECK` constraint to be used (with a foreign key) so in context it **is** necessary. But it can be 'hidden' via a view if it bothers you. – onedaywhen Apr 13 '12 at 13:00
I think it's a valid solution. When you think about it though plugintype,pluginid is not really a key because (1,'foo'),(1,'bar) should not be allowed in the plugins table. In my case I was trying to use a constant as part of the key and postgres didn't like it. I preferred the selected answer because it did the job without having to modify the data types. – hsikcah Apr 13 '12 at 14:45
Sorry, you're right. I guess I should wait for the caffeine to kick in before posting in the morning. – kgrittn Apr 13 '12 at 14:47

kgrittn · Answer 3 · 2012-04-13T15:34:21.973

0

One way of handling this is to use serializable transactions.

http://wiki.postgresql.org/wiki/SSI#FK-Like_Constraints

edited Apr 13 '12 at 15:34

answered Apr 13 '12 at 12:32

kgrittn

18,113
3
39
47

Well, this can't be a foreign key without some denormalization (redundantly storing data in matrix_params) or declaring as IMMUTABLE a function which is really STABLE, which carries certain risks. (When you lie to the database, it tends to get even with you sooner or later.) So the only *really* accurate answer would be that it can't be done. But that's not very helpful. This is a very common type of use-case, and exactly the sort of thing serializable transactions are intended to address. Maybe my link should have been here: http://wiki.postgresql.org/wiki/SSI#FK-Like_Constraints – kgrittn Apr 13 '12 at 14:53

How to make a foreign key with a constraint on the referenced table in PostgreSQL

3 Answers3

Linked