CODEWITHSUNDEEP
sqlpostgresqlconstraints
yeahboy
yeahboy

Reputation: 97

How to set a conditional constraint in postgreSQL

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.

Upvotes: 0

Views: 870

Answers (1)

yeahboy
yeahboy

Reputation: 97

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.

Upvotes: 1

Related Questions