postgres check foreign table column

Question

Is it possible to create a check saying:

"in computer_system, check if ram (from ram_table) matches ram_type (from motherboard_table)"

I've been all over google and stackoverflow, and I've read it was 'impossible' to refer to foreign table column in a check ? But is there a work-around, or how is my logic flawed? Any help is appreciated!

(Edited for relevant code)

create table computer_system(
    system_id int primary key,
    ram int not null references ram(ram_id),
    cpu int not null references cpu(cpu_id),
    mb int not null references motherboard(mb_id),
    case int not null references computer_case(case_id),
    graphics int references gfx(gfx_id)
);

create table motherboard(
    mb_id int primary key,
    ram_type varchar(10) not null,
    socket varchar(10) not null,
    form_factor varchar(30) not null,
    ob_gfx boolean default(false)
)

create table ram(
    ram_id int primary key,
    speed int not null,
    size int not null,
    type varchar(10) not null
)

FYI, You should wait a little bit before accepting an answer. Some people won't even look at your question because it's answered and you'd miss another point of view. You can always upvote an answer when you find it usefull :-) — Luc M
– Luc M, Commented Mar 11, 2014 at 12:55

Craig Ringer · Accepted Answer · 2014-03-11 12:50:42Z

4

You cannot do this with a CHECK constraint in PostgreSQL.

(Theoretically PostgreSQL could allow this, but it'd need to create triggers behind the scenes to check the dependent tables, and it doesn't know how to discover and prove the relationships).

You will need to create a BEFORE INSERT OR UPDATE trigger on computer_system. The trigger must run a query that fetches the corresponding rows in ram and motherboard and compares their type values.

Something like the un-tested:

CREATE OR REPLACE FUNCTION ram_type_matches() RETURNS trigger AS $$
DECLARE
    mb_ram_type text;
    ram_ram_type text;
BEGIN
    mb_ram_type  := (SELECT type FROM ram WHERE ram_id = NEW.ram)
    ram_ram_type := (SELECT ram_type FROM motherboard WHERE mb_id = NEW.mb)
    IF mb_ram_type <> ram_ram_type THEN
      RAISE EXCEPTION 'Mismatch between motherboard RAM type % and selected module type %', mb_ram_type, ram_ram_type;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ram_type_matches_tg
BEFORE INSERT OR UPDATE ON computer_system
FOR EACH ROW EXECUTE PROCEDURE ram_type_matches();

Strictly you should also create ON UPDATE triggers on motherboard and ram, preventing the type value from changing after record creation.

edited Mar 11, 2014 at 12:50

answered Mar 11, 2014 at 12:08

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3405863 Over a year ago

I'd be able to create the view, but how to use that view? Would it be too much to ask for an example of the view, and how that should be used in connection with the "computer_system"-table ?

Craig Ringer Over a year ago

I misunderstood the intent of the question. I've revised my answer.

Collectives™ on Stack Overflow

postgres check foreign table column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related