How to insert into two tables connected by foreign key

[zggame]

Hi,

I have two tables with 1:1 relationship. Table Product and table
Format. Format.ProductID is a foreign key (=Product.ID). I would
insert data to them simultaneously. Currently, we do it in three
steps.

1. insert into Product (...)
2. select max(ID) from Product return x
3. insert into Format (ProductID,....) value (x, ...)

It works, but prone for problems, say, failing in one step will lead
to corrupt data. I am wondering if I can do that in one step in
mysql. Thanks.

Sincerely
Zhu, Guojun

[Gordon Burditt]

Post by zggame
I have two tables with 1:1 relationship. Table Product and table
Format. Format.ProductID is a foreign key (=Product.ID). I would
insert data to them simultaneously. Currently, we do it in three
steps.
1. insert into Product (...)

Presumably, Product.ID here is an auto_increment field, otherwise
you'd know what the value here is without having to select it.

Post by zggame
2. select max(ID) from Product return x

max(ID) is *NOT* necessarily the ID of the record you just inserted.
(especially if this code is on a web page where multiple users can
use it). It might be the ID of some record inserted after that.

Post by zggame
3. insert into Format (ProductID,....) value (x, ...)

2. insert into Format (ProductID, .....) values (last_insert_id(), ...)

Note: last_insert_id() works on a *PER-CONNECTION* basis, so other
instances of this code also inserting new records won't affect
last_insert_id(). That situation will, however, screw up max(ID)
if the timing happens to turn out wrong.

If you are using InnoDB tables, you can combine these two queries
into one transaction.

Post by zggame
It works, but prone for problems, say, failing in one step will lead
to corrupt data. I am wondering if I can do that in one step in
mysql. Thanks.

[zggame]

Post by Gordon Burditt

1.  insert into Product (...)

Presumably, Product.ID here is an auto_increment field, otherwise
you'd know what the value here is without having to select it.

Yes. That is what we did.

Post by Gordon Burditt

3.  insert into Format (ProductID,....) value (x, ...)

2.  insert into Format (ProductID, .....) values (last_insert_id(), ...)
Note:  last_insert_id() works on a *PER-CONNECTION* basis, so other
instances of this code also inserting new records won't affect
last_insert_id().  That situation will, however, screw up max(ID)
if the timing happens to turn out wrong.

Is this last_insert_id() a build-in function of mysql? I am using
java (jdbc). What do you mean "per-connection basis"? Does that mean
that I need to use Statement generated by the same Connection
object?

Post by Gordon Burditt
If you are using InnoDB tables, you can combine these two queries
into one transaction.

We do use InnoDB engine and we enforced the foreign key. Could you
please explain this a bit more? Thank you very much.

[Gordon Burditt]

Post by zggame

Post by Gordon Burditt

1. =A0insert into Product (...)

Presumably, Product.ID here is an auto_increment field, otherwise
you'd know what the value here is without having to select it.

Yes. That is what we did.

Post by Gordon Burditt

3. =A0insert into Format (ProductID,....) value (x, ...)

2. =A0insert into Format (ProductID, .....) values (last_insert_id(), ...=

)

Post by Gordon Burditt
Note: =A0last_insert_id() works on a *PER-CONNECTION* basis, so other
instances of this code also inserting new records won't affect
last_insert_id(). =A0That situation will, however, screw up max(ID)
if the timing happens to turn out wrong.

Is this last_insert_id() a build-in function of mysql?

Yes.

Post by zggame
I am using
java (jdbc). What do you mean "per-connection basis"? Does that mean
that I need to use Statement generated by the same Connection
object?

I don't really understand how you use MySQL from Java, but you've
probably got the right idea here. You connect (on a network level,
by TCP connection) to MySQL, giving login and database info. Then
you do a series of queries and get results. Sometimes previous
queries affect later ones (such as SET queries, or side effects on
last_insert_id() or user variables). Then you close the connection.
The connection would probably be represented as an object in Java.

"Per-connection" means that changes made to one connection do not
affect other connections. Examples of such things include the
current database, the value of last_insert_id(), the value of local
user variables, the character set of the connection, etc.

Post by zggame

Post by Gordon Burditt
If you are using InnoDB tables, you can combine these two queries
into one transaction.

We do use InnoDB engine and we enforced the foreign key. Could you
please explain this a bit more? Thank you very much.

If you don't know what a transaction is, I'm not going to try and
teach you how to use them (books on the subject can get pretty
difficult for one person to lift). A transaction is a sequence of
queries that appear to the outside (other users/connections) to
either all happen at once or none happen. MySQL transactions work
on InnoDB tables but not MyISAM tables. Transactions are less
restrictive than locking all the tables. Example:

SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO Product values (.....);
INSERT INTO Format values (......);
COMMIT;

Other connections will not see the intermediate state where you
have a Product record and no Format record. If something botches
one query, such as the INSERT into the Format table, previous queries
such as the insert into the Product table will be rolled back.

[zggame]

Thank you very much. JDBC is the java API for sql (including mysql).
We use InnoDB mostly to enforce the integrity check on the foreign
key. In JDBC, there is this concept of batch-statement and roll back,
which is very much like the transaction idea you describes. I will
read more about this. Also, the new version JDBC can gives out the
last insert id directly when calling insert statement, no need to call
"select last_insert_id". (I just learned from the mysql reference. ^-
^ My book is too old and JDBC in that has no such function.) Thanks
again and have a good night.

[zggame]

Actually, we have another table also with foreign key (productID)
associated to product table. We also want to insert this one at the
same time. Will the last_insert_id() work or is there any other
work_around? Thanks.

[zggame]

Gordon:

I checked out mysql reference with your hint. And found out this

http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html

Now I only have the question about the innoDB. Could you please
explain this a bit? Thanks.

[Gordon Burditt]

Post by zggame
Actually, we have another table also with foreign key (productID)
associated to product table. We also want to insert this one at the
same time. Will the last_insert_id() work or is there any other
work_around? Thanks.

last_insert_id() may be changed whenever you finish doing an insert.
If you need to save the value of last_insert_id() for use in TWO
(or more) inserts, there are various methods to save the value,
including:
SELECT last_insert_id();
and the use of user variables. User variables make it easier to
fire off a series of queries without needing results from one to
compose the next query.

SELECT last_insert_id();
although a separate query, still has major advantages over
SELECT max(ID) from table;