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. Here's some background to my problem. Table (X) is referenced by two other tables (Y & Z). This are one-to-many relationships (for every record in X there are 0 or more records in both Y, and in Z). Now.. I also have a many-to-many relationship between Y & Z (for every record in Y there are 0 or more records in Z, and for every record in Z there are 0 or more records in Y). So, I created a table Y_Z to keep track of this relationship. If I remove the check constraint that is causing the error, how can I ensure that all Y_Z entries associate entries that reference the same record in X? In other words, say I have.. insert into X (xid) values(1); insert into X (xid) values(2); insert into Y (yid, y_xid) values (1, 1); /* references entry 1 in X */ insert into Z (zid, z_xid) values (1, 2); /* references entry 2 in X */ I need a constraint that would fail the following statement, and maintain my data integrity. insert into Y_Z( yz_yid, yz_xid) values (1, 1). Is there any alternative you can suggest? Thank you in advance. German _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users