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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
