German Escallon <germ...@adtecinc.com> wrote:
> Hello all,
>
> I'm facing a challenge, and I hope someone can give me a hand.
>
> I tried something along the lines of:
>
> create table X(xid integer primary key);
> create table Y(yid integer primary key, y_xid references X(xid) );
> create table Z(zid integer primary key, z_xid references X(xid) );
>
> create table Y_Z
> (
> yzid integer primary key autoincrement,
> yz_yid references Y(yid), /*references primary key in y */
> yz_zid references Z(zid), /* references primary key in z */
> CHECK
> (
> (select y_xid from Y where yid= yz_yid ) = (select z_xid from
> Z where zid= yz_zid )
> )
> );
>
> Jusding by this diagram
> <http://www.sqlite.org/syntaxdiagrams.html#table-constraint> I thought
> my last create table statement was syntactically correct. But I got
> the following message:
> "Error: subqueries prohibited in CHECK constraints".. Uhmm.. Bummer.
>
> Is there any alternative you can suggest? Thank you in advance.
You could have insert and update triggers on Y_Z to enforce the constraint.
--
With best wishes,
Igor Tandetnik
With sufficient thrust, pigs fly just fine. However, this is not necessarily a
good idea. It is hard to be sure where they are going to land, and it could be
dangerous sitting under them as they fly overhead. -- RFC 1925
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
