Julian Pellico <[EMAIL PROTECTED]> wrote on 04/04/2005 06:38:57 PM: > Hello, > > I'm using MyISAM tables in mysql and in order to make a certain > operation appear atomic, I need to insert records into 2 tables in a > certain order. In particular, in one of the tables is a key that maps > to multiple rows in the other table. This is the sane thing to do if > there were'nt any order requirements: > > CREATE TABLE Bar ( > ... other stuff ... > key1 int unsigned not null auto_increment, > UNIQUE (key1), > ... > ); > CREATE TABLE Foo ( > key1 int unsigned not null, > key2 int unsigned not null, > value int, > PRIMARY KEY k (key1, key2) > ); > > However, I need to insert a set of rows into Foo first, all with the > same key1. But I need an ID that is unique to Bar! My 1st solution is: > CREATE TABLE Bar ( > ... other stuff ... > key1 int unsigned not null, > UNIQUE (key1), > ... > ); > CREATE TABLE Foo ( > key1 int unsigned not null, > key2 int unsigned not null, > value int, > PRIMARY KEY k (key1, key2) > ); > CREATE TABLE BunchaIDs ( > key1 int unsigned not null auto_increment primary key > ); > then what I can do is > INSERT INTO BunchaIDs VALUES (); > id = LAST_INSERT_ID(); > INSERT INTO Foo (key1, key2, value) VALUES ......<buncha values withkey1=id>; > INSERT INTO Bar (...., key1) VALUES (..., id); > > Unfortunately, using another table is rather complicated because there > is actually a set of tables like Foo and Bar that are organized by > days, so I would need an ID table for every day, which adds to > maintenance operations... > > So my 2nd solution was I found I could do: > CREATE TABLE Bar ( > ... other stuff ... > key1 int unsigned not null, > UNIQUE (key1), > ... > ); > CREATE TABLE Foo ( > key1 int unsigned not null auto_increment, > key2 int unsigned not null, > value int, > PRIMARY KEY k (key1, key2) > ); > then I could do something a little unorthodox... > INSERT INTO Foo (key2, value) VALUES (1, 100); > id = LAST_INSERT_ID(); > INSERT INTO Foo (key1, key2, value) VALUES (id, 2, 101), (id, 3, 42), > (id, 4, 77), .....; > INSERT INTO BAR (...., key1) VALUES (..., id); > > I insert one record in Foo to get a unique id for key1, then insert > the rest of the records into Foo specifying the same value for key1. > Since records for Bar are always preceded by records for Foo, the key > in Bar will be unique. In addition, the next time I insert into Foo > *without* giving an id, mysql gives me a unique id, presumably one > higher than the last. > > The questions are, is this use of auto_increment portable across SQL > databases? How sound is it across versions of MySQL and into the > future? Are there any possible problems I could run into? > > Thanks, > Julian > Julian,
It sounds as though you are putting the cart before the horse. You need to insert the record into Bar first in order to get the key1 value. What is it that is preventing you from making the Bar insertion first? How can you possibly have the Foo records before the Bar record is available? Please be just a bit more specific, if possible. We can continue to use bogus table names but I need to know just a little more about your process so that I can understand why you cannot create your Bar record first. To answer your question: no, I do not think this is a good use of auto_increment. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
