Here are 2 tables to use in our example:
CREATE TABLE t1 (
t1_pk INTEGER PRIMARY KEY,
t1_value VARCHAR(20)
);
CREATE TABLE t2 (
t1_fk INTEGER,
t2_id INTEGER,
t2_value VARCHAR(20)
);
t1_pk would be the primary key on your main table (t1), t1_fk would be
foreign key to t1_pk and t1_fk+t2_id (not mathematical addition) would
form a unique identifier for any t2 record.
Since t1_pk is declared as an INTEGER PRIMARY KEY it will always hold
the same value as ROW_ID. So once you have called sqlite_exec() with
your insert statement for t1, call last_insert_rowid() and use that
value as the value for t1_fk when you will do yhe insert in t2.
Hope this can help you a bit, if still not sure about how to proceed,
just mail me back.
Simon B.
On Tue, 2004-04-06 at 00:01, Rob Duncan wrote:
> Greetings,
>
> I have what is, I think, a very basic SQL question, but I don't seem to
> be able to find the answer. I'm almost a complete database novice, so
> perhaps I have things completely wrong… I want to insert a row into
> one table and have it automatically generate a primary key (the other
> fields in the insert are not going to be unique, only the primary key
> is sure to be unique). I then want to add some rows to other tables
> that refer to the first row with a foreign key. So my question is, how
> do I obtain the value of the first row's primary key, and how do I use
> it as a foreign key in the subsequent inserts?
>
> The last_insert_rowid() function seems to be close to what I want. The
> trouble with it is that as soon as I add a row to the first child table
> its value will change and I will not be able to use it for any other
> child inserts.
>
> Thanks,
>
> Rob.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
