On Sun, Jul 18, 2010 at 06:24:33PM +0000, [email protected] scratched on the
wall:
>
> I have never worked with 2 tables in a many-to-many relationship and
> could use some help.
>
> given:
> table a
> _id integer primary key,
> a_data varchar(40);
>
> table b
> _id integer primary key,
> b_data varchar(40);
>
> I should define a 3rd table -
>
> table a_b
> aID integer not null,
> bID integer not null;
> where -
> aID is the _id field from the "a" table
> bID is the _id field from the "b" table
Yes, that's the general idea.
BTW, I strongly recommend you keep key names globally unique. For
example, the key column of "a" should be "a_id", not "_id." Same
with "b". That way, there is no confusion over what the column "_id"
refers to... and since you're very likely to join these three tables
together, the "a" keys and "b" keys are likely to end up in the same
result table.
Use the same names for the link table (or any FK). Don't call it
"aid", but "a_id". Using constant names also allows you to use the
USING or NATURAL JOIN syntax.
> My questions are:
>
> What would an "insert" statement look like that inserts data into
> table "a" and table "b", gets the value of the "_id" fields of each
> table after the insert, and inserts those 2 values into the a_b table?
You need to do each step manually. Just insert/find the data in "a"
you want to link. Do the same on "b". Insert the middle row. Do it
all in a transaction so it is applied to the database as one
operation. As for finding the _id values, you can use the
last_insert_rowid()[SQL] or sqlite3_last_insert_rowid()[C] function,
like you might use for any other key.
You can't do this kind of thing with one complex INSERT.
> Do I need a primary key field in the a_b table?
You don't NEED it, but it would be a good idea.
> If so, would it be a combination of the (2) _id fields
> already present in that table.
Yes. That will keep memberships unique. Each individual column is
likely to have duplicate values, so that's really your only choice.
> If so, how do I specify that?
CREATE TABLE a_b_link (
a_id INTEGER NOT NULL REFERENCES a( _id ),
b_id INTEGER NOT NULL REFERENCES b( _id ),
PRIMARY KEY ( a_id, b_id )
);
The PK will make an index over a_b_link(a_id,b_id). Chances are
good you'll also want an index over a_b_link(b_id,a_id)
Also, don't forget you'll need to turn FK constraints on, as they're
off by default.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
