CODEWITHSUNDEEP
mysqldatabase
Franek
Franek

Reputation: 143

Inserting into two MySQL related tables with surogate primary keys

In my database, all Primary Keys are surogate. There are some Unique keys, but not always, so the most safe way to access specific row is Primary Key. Many of them use AUTO_INCREMENT. Do I have to lock access to database when inserting into two related table? For example. 

create table foo 
( 
    foo_id numeric not null auto_increment, 
    sth varchar, 
    PRIMARY KEY(foo_id)
)

create table bar 
( 
    bar_id numeric not null auto_increment, 
    foo_id numeric not null, 
    PRIMARY KEY(bar_id), 
    FOREIGN KEY (foo_id) REFERENCES foo(foo_id)
)

First I insert sth to foo, and then I need foo_id value to insert related stuff into bar. This value I can get from INFORMATION_SCHEMA.TABLES. But what if somebody will add new row into foo before I get the auto_increment value? If all these steps are in stored procedure is there implicitly started transactions which locks all needed resources for one procedure call? Or maybe I have to use explicitly START TRANSACTION. What if I dont use procedure - just sequence of inserts and selects?

Upvotes: 3

Views: 32

Answers (2)

devpro
devpro

Reputation: 16117

Run queries in that sequence:

INSERT INTO foo (sth) VALUES ('TEST');

Than:

INSERT INTO bar (foo_id) VALUES (SELECT LAST_INSERT_ID());

Upvotes: 0

Richard St-Cyr
Richard St-Cyr

Reputation: 995

Instead of looking in INFORMATION_SCHEMA.TABLE, I would suggest that you use LAST_INSERT_ID.

From the MySQL documentation: The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

This imply that an insert done at the same time on a different connection will not change the value that is returned on your current connection.

Upvotes: 3

Related Questions