How to set a conditional constraint in postgreSQL

Question

I'm setting up a database for a computer store. In the database I have a table called computer_system, which has an array attribute. In the array these components go: cpu ram case mainboard graphics card

Now to the tricky part: The different components have their own tables and are all child tables of a table called component. The mainboard table has an attribute called "on-board graphics". If it has a graphics card, it will be listed here. If not, the attribute is null.

Now, when I insert components in the computer_system table, I need postgres to check if the mainboard passed in, has an on_board graphics card. If not, a graphics card must be added, before the query can execute succesfully. How do I add this constraint?

I tried to explain my issue the best that I can, and I have checked all my books, but I cannot find the answer to this. Feel free to ask me, if something was unclear. Also, here is an unfinished ER-diagram I've made for this database

https://i.sstatic.net/zAT0f.png

EDIT: In other words what I want to do is, if mainboard.on_board_graphics is NULL, then graphics_card cannot be NULL.

Components and their characteristics change so quickly, that you'll have to redesign your schema every single day. Looks like JSON format is more suited for this, with constraints on application side. — Egor Rogov
– Egor Rogov, Commented Mar 19, 2016 at 10:50
@EgorRogov This is a school assignment, so I will not have to update the database depending on the real world's characteristics of components :) — yeahboy
– yeahboy, Commented Mar 19, 2016 at 11:05
Well, without seeing your tables it's hard to suggest something. But if you allow at most one graphics card to be attached to a motherboard, you can have FK like this: mainboard.graphics_card_id references graphics_card(id). Then you can add constraint check ((on_board_graphics is not null) or (graphics_card_id is not null)). — Egor Rogov
– Egor Rogov, Commented Mar 19, 2016 at 11:40
@EgorRogov on_board_graphics is an attribute in another table, so I cannot access it from a CHECK constraint. But I found another way, and have answered my own question. Thanks for your feedback though! — yeahboy
– yeahboy, Commented Mar 19, 2016 at 13:20
So, the sqlfiddle is very stupid. Take a look at this paste. The problem solved with trigger. — Ivan Black
– Ivan Black, Commented Mar 19, 2016 at 13:21

yeahboy · Accepted Answer · 2016-03-19 13:19:31Z

1

The obvious way is to perform a CHECK constrain, which would not allow graphics_card and on_board_graphics to be null at the same time. But on_board_graphics is in another table, and since postgresql does not allow subquery's in CHECK's, this was not a solution.

I solved this by adding a function and a trigger like this:

CREATE FUNCTION graphics_guaranteed() RETURNS TRIGGER AS $$ 
BEGIN

IF 
(SELECT on_board FROM mainboard WHERE NEW.mainboard = mainboard.name) IS NULL
AND
NEW.graphics_card IS NULL
THEN
RAISE EXCEPTION 'graphics not found';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER graphics_trigger
AFTER UPDATE OR INSERT ON computer_system
FOR EACH ROW
EXECUTE PROCEDURE graphics_guaranteed();

Hope this helps, if some one else stumbles upon the same problem.

answered Mar 19, 2016 at 13:19

yeahboy

9710 bronze badges

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

Collectives™ on Stack Overflow

How to set a conditional constraint in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related