Re: [sqlite] Multi-Table constraint

[Igor Tandetnik]

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