MySQL auto_increment keys in InnoDB vs MyISAM
So here is a typical data model pattern:
create table master ( mid int not null auto_increment, … , primary key (mid) );
create table detail ( mid int not null, did int not null auto_increment, … , primary key (mid, did) );
( detail.mid is a foreign key to master.mid but no one uses these in MySQL… )
So this works great if you’re using the default MyISAM storage manager but it does not work using the InnoDB storage manager (RTFM!). Unfortunately, MyISAM does not provide ACID properties which means you should not be using MyISAM unless you have another way of recovering from data loss. My data is valuable so I have to use InnoDB, which means I have to restructure the detail table so that detail.did is the primary key and detail.mid is just a field with an index.
I really wish MySQL had one storage manager that actually worked like a relational database is supposed to, instead of several half-working storage managers.
February 7th, 2008 at 10:43 pm
Or you could use Postgresql maybe? A few years ago I investigated Postgresql after finding that MySQL was silently truncating over-long text fields rather than raising an error. I liked what I saw (I have an Oracle background) and I’ve used Postgresql ever since, very happily.