I am attempting to give a constraint to some table by checking that the sum of the something is < 100.
This really is my schema:
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 have to make sure that the sum of the percentage_time for every eid is <= 100.
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, it will have errored after i added the next entry for eid because the sum > 100.
It could have been acceptable for eid 1 and a pair of as percentage_time <= 100
How is this done?
To date all I have done is
ALTER TABLE Works ADD CONSTRAINT c1 CHECK SUM(pct_time) < 100
You need to use a trigger with this. Allow it to be see if
SELECT SUM(pct_time) FROM Works GROUP BY eid has any values which will review 100 after your place. If it's the situation, throw a mistake.
Are you currently acquainted with triggers? What database system are you currently using?
EDIT: Browse the documentation for triggers here. Essentially what you would like is one thing such as this:
CREATE TRIGGER Work_insert_trigger BEFORE INSERT ON Works FOR EACH ROW EXECUTE PROCEDURE check_sum_work();
check_sum_work() procedure will see if any SUM(percentage_time) > 100.
Here may be the documentation for trigger methods, be aware from the
NEW special variable which enables you to definitely pick out the element being placed. So you will want to
SELECT SUM(pct_time) FROM Works WHERE eid = NEW.eid and find out in the event that sum +
NEW.pct_time is more than 100.
This may sounds pretty complicated if you have never witnessed triggers before, but it is really quite a effective mechanism that you will learn how to appreciate. :)
Here is yet another illustration of PostgeSQL triggers getting used.