Add a contraindication that checks the amount

Question

I am trying to add a constraint to the table by checking that the sum of the value is <100.

This is my diagram:

CREATE TABLE Works ( eid INTEGER, did INTEGER, pct_time INTEGER, PRIMARY KEY (eid,did), FOREIGN KEY (eid) REFERENCES Employee(eid), FOREIGN KEY (did) REFERENCES Dept(did) ); 

I need to check that the sum of pct_time for each eid is <= 100.

for instance

 eid --- did ---- pct_time 0 ----- a ------- 50 0 ----- d ------- 40 0 ----- c ------- 20 1 ----- a ------- 90 1 ----- b ------- 10 2 ----- d ------- 40 2 ----- a ------- 20 

Here he should have an error when adding a third record for eid 0 as a sum> 100.
It would be good for eid 1 and 2 as pct_time <= 100
How can I do that?

So far all I have done

 ALTER TABLE Works ADD CONSTRAINT c1 CHECK SUM(pct_time) < 100 

Answer 1

For this you need to use a trigger. Check to see if the SELECT SUM(pct_time) FROM Works GROUP BY eid values ​​that will exceed 100 after insertion. If so, enter the error.

Do you know triggers? What database system are you using?

EDIT: Check out the documentation for triggers here . Basically what you want is something like this:

 CREATE TRIGGER Work_insert_trigger BEFORE INSERT ON Works FOR EACH ROW EXECUTE PROCEDURE check_sum_work(); 

Then your check_sum_work() routine check_sum_work() check to see if SUM (pct_time)> 100.

Here is the documentation for the trigger procedures, pay attention to the special variable NEW , which allows you to select the element inserted. So you want SELECT SUM(pct_time) FROM Works WHERE eid = NEW.eid and see if this amount exceeds + NEW.pct_time 100.

This may seem rather complicated if you have never seen triggers before, but it’s actually a pretty powerful mechanism that you will learn to appreciate. :)

Here is another example of using PostgeSQL triggers.