On 31 Jul 2014, at 10:26pm, Richard Warburton <rich...@skagerraksoftware.com> wrote:
> I'm looking for an elegant way to prevent enrolments having enrolmentItems > if leaver is set to 1. > [...] > Ideally, I'd like a check in enrolmentItem that can examine a different > field in the referenced enrolment record. TRIGGERs are old and cold. FOREIGN KEYs are teh noo secsay. How about coding it into a FOREIGN KEY requirement for enrolmentItem ? You would normally do something like FOREIGN KEY(enrolment) REFERENCES enrolment(id) ON DELETE RESTRICT ON ACTION RESTRICT but you might be able to do FOREIGN KEY(enrolment,0) REFERENCES enrolment(id,leaver) ON DELETE RESTRICT ON ACTION RESTRICT or possibly FOREIGN KEY(0,enrolment) REFERENCES enrolment(leaver,id) ON DELETE RESTRICT ON ACTION RESTRICT To make this happen you'd want an index on the enrolment table which reflected the foreign key. I'm not actually sure whether you can use constants in foreign keys like this. You'll have to try it. If it's not allowed, you'll need to create a dummy column in enrolmentItem which always contains 0. See section 3 -- well, all of it, really -- of <http://www.sqlite.org/foreignkeys.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
