On Fri, 1 Aug 2014 09:26:14 +1200 Richard Warburton <rich...@skagerraksoftware.com> wrote:
> 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? Check http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012 for parent-child relationship example, it's for in-table relation but the in-code documentation is awesome, you can "steal" some ideas from it. > > Thanks. --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
