[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Only allow child record if another field in parent is false.
From:       Simon Slavin <slavins () bigfraud ! org>
Date:       2014-07-31 21:53:51
Message-ID: A82AE8B5-75C8-4136-9DC0-A252B661F353 () bigfraud ! org
[Download RAW message or body]


On 31 Jul 2014, at 10:26pm, Richard Warburton <richard@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


[prev in list] [next in list] [prev in thread] [next in thread]