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 with key1=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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
