On Fri, 1 Aug 2014 09:26:14 +1200
Richard Warburton <rich...@skagerraksoftware.com> wrote:
> 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.
IIUC, you have tables A and B, and the rules are
you can add/delete rows in B while A.bool is false
you cannot modify B while A.bool is true
A.bool can only become true; it cannot revert to false
This isn't a referential integrity problem, and shouldn't be dealt with
in the DDL. That's not the answer you wanted, I know. Please allow me
to explain.
RI boils down to one thing: If X exists, Y must exist. It does not say
anything about the state of Y or the number of Xs. Its only job is to
prevent the database equivalent of a dangling pointer: a reference to
something nonexistent.
I considered a few quasiclever ways to express your rules in RI terms,
but they would require the application to insert/delete/update rows in
other tables to express the boolean state. For example, you could
have a DoNotUpdate table with FK references to every column in table B
("items"). Then you don't need your boolean column, but you do have to
insert the rows in the new table instead. It's not clear the database
or the application is thus improved.
Any rule "do not modify B if X is true in A" (where X is not
manifested elsewhere in the database) is not expressible in relational
terms. It's a business rule, and should be encoded in the application
logic.
As a matter of fact, there probably will come a time when someone with
sufficient authority *will* revert the boolean to false and *will*
delete/update the item rows. Probably to correct an error. In so
doing, from a data modelling perspective, he'll be exercising some
authority not given to everyone to modify the database's representation
of the external reality that it reflects. But he won't have turned the
data into nonsense, won't have violated the database's logical
constraints. That alone tells you the difference.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
