Hi, Consider:
CREATE TABLE enrolment ( id INTEGER PRIMARY KEY, child INT NOT NULL REFERENCES child (id), start INT NOT NULL, leaver INT NOT NULL DEFAULT 0 CHECK (leaver BETWEEN 0 AND 1), -- BOOL ); CREATE TABLE enrolmentItem ( id INTEGER PRIMARY KEY, enrolment INT NOT NULL REFERENCES enrolment (id) ON DELETE CASCADE, day INT NOT NULL, start INT NOT NULL, end INT NOT NULL CHECK (start < end), ); I'm looking for an elegant way to prevent enrolments having enrolmentItems if leaver is set to 1. This way, you can't change the leaver field if enrolmentItems are attached, and you can't add an enrolmentItem if leaver is set to 1. I think 2 triggers could work, but it seems overkill. I could have a leaver field in enrolmentItem referencing leaver in enrolment and put a check against that, but I'd be storing unneeded data. Ideally, I'd like a check in enrolmentItem that can examine a different field in the referenced enrolment record. Suggestions? Thanks. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
