On Sun, Jul 18, 2010 at 06:24:33PM +0000, c...@comcast.net 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to