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
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
