Uhmm.. Interesting. Thanks for your help.
Jay A. Kreibich wrote: > On Fri, Feb 05, 2010 at 06:42:34PM -0500, German Escallon scratched on the > wall: > > >> 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 ) >> ) >> ); >> > > > OK, this is a tad on the ugly side, but it might work. Basically I'm > having rows in y_z carry around a copy of the xid from the two > tables. The CHECK constraint then becomes simple. I've fiddled > with the names a bit to keep things straight in my head. > > Yes, some of the UNIQUEs are redundant, but they're required for the > FKs to work correctly. > > ---------------------------------------------------------------------- > PRAGMA foreign_keys = 1; > > CREATE TABLE x ( xid INTEGER PRIMARY KEY ); > CREATE TABLE y ( yid INTEGER PRIMARY KEY, > xid INTEGER REFERENCES x ( xid ), > UNIQUE ( yid, xid ) ); > CREATE TABLE z ( zid INTEGER PRIMARY KEY, > xid INTEGER REFERENCES x ( xid ), > UNIQUE ( zid, xid ) ); > > CREATE TABLE y_z ( yzid INTEGER PRIMARY KEY AUTOINCREMENT, > yid INTEGER, > y_xid INTEGER, > zid INTEGER, > z_xid INTEGER, > > FOREIGN KEY ( yid, y_xid ) REFERENCES y ( yid, xid ), > FOREIGN KEY ( zid, z_xid ) REFERENCES z ( zid, xid ), > CHECK ( y_xid == z_xid ) > ); > > INSERT INTO x ( xid ) VALUES ( 1 ); > INSERT INTO x ( xid ) VALUES ( 2 ); > > INSERT INTO y ( yid, xid ) VALUES ( 111, 1 ); > INSERT INTO y ( yid, xid ) VALUES ( 112, 1 ); > INSERT INTO y ( yid, xid ) VALUES ( 121, 2 ); > INSERT INTO y ( yid, xid ) VALUES ( 122, 2 ); > > INSERT INTO z ( zid, xid ) VALUES ( 211, 1 ); > INSERT INTO z ( zid, xid ) VALUES ( 212, 1 ); > INSERT INTO z ( zid, xid ) VALUES ( 221, 2 ); > INSERT INTO z ( zid, xid ) VALUES ( 222, 2 ); > > -- this should work: > INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 211, 1 ); > > -- fails check constraint: > INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 221, 2 ); > > -- fails foreign key constraint ( there is no y(221,1) ) > INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 221, 1 ); > ---------------------------------------------------------------------- > > Of course, the INSERT on y_z gets... interesting. There are ways to > reduce that, however. Maybe something like: > > INSERT INTO y_z ( yid, y_xid, zid, z_xid ) > VALUES ( ?1, ( SELECT xid FROM y WHERE yid = ?1 ), > ?2, ( SELECT xid FROM z WHERE zid = ?2 ) ); > > You might even be able to work that into a trigger so you can just > insert yid and zid and the trigger fills in the y_xid and z_xid. > > Actually... come to think of it, you might just build a trigger into > your original schema. You can't put a sub-select into a CHECK, but > you can put one in a trigger. Try an INSERT/UPDATE trigger that > makes sure all the references line up and throws an error if they > do not. > > -j > > > >> 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. >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
