Error 1005 when creating tables on some servers.

[AmiMitra]

i am using mysql 5.1. i have one table named as resv_record. the format is

create table resv_record ( book enum('h','c','r','f','t') , resv_id int(8) auto_increment not null , user_id varchar(12) not null , primary key( book , resv_id))engine=myisam;

i am creating a new table named flight_book as

create table flight_booking ( resv_id int(8) not null , jrny_dt date not null , from_place varchar(20) not null, destination varchar(20) not null , class varchar(20) not null , flight_pref1 varchar(50) ,flight_pref2 varchar(50),flight_pref3 varchar(50), primary key (resv_id) , foreign key (resv_id) references resv_record(resv_id));

and i am having an error which says...can't create table(err 150) ...error no 1005.
please help.

[prabirchoudhury]

hey .. it seems to be alright to me .. this error happening when you do mistake on the foreign key data type mismatch with the original primary key data type. but here looks ok. i did tested on mysql >5 and could create both table and no error . it might have other problem ..

good luck ...

[AmiMitra]

actually i created both the tables in one machine and it did not give any error but when trying to do the same in another machine...givin g this error... i simply dont know what to do...

[Atli]

Hi.

I'm guessing the problem is that you are creating a foreign key in a non-engine-specific table that is set to reference a field in a table specifically marked as a MyISAM table.
InnoDB tables referencing fields in MyISAM tables should give you an error like that.

If you do not specify the engine (like your second table) the default engine will be used. The engine used by default depends on the server, so servers set up to use InnoDB will give you this error, while those set up to use MyISAM will not.

MyISAM tables do not enforce foreign key relationships, so a MyISAM table created with a broken foreign key should (for now) work fine. (Except for not enforcing the key, obviously)

So, to solve this try to do any of the following:

• Specifically mark both tables as InnoDB. (Using the engine clause, like in your first table.)
• Specifically mark both tables as MyISAM and remove the foreign key restraint. (It won't be used anyways, so why bother having it?)
• Don't specify the engine used for either table and let the server decide what to do.

[AmiMitra]

if i specify the first table as innodb or dont specify anything at all...then the first table cannot be created..it gives an error which says ...auto increment should be assigned to only one column and that must be declared as primary key..only myisam lets me create the table as i want it.

i didnot try specifying the 2nd table as myisam...but as u told...that table wont use the foreign key constraint.

so what to do now?

[Atli]

Ok.

So apparently, compound primary keys that include an auto_increment field do work in MyISAM, but not in InnoDB.
(See 13.6.14. Restrictions on InnoDB Tables and Bug #15109)

Which gives you two choices.

1. Define both tables as InnoDB and define `resv_id` in `resv_record` as a Key as well as including it in the Primary Key. (Or simply remove `book` from the Primary Key)
   This will preserve the FK constraint, but it will not give you a independent `resv_id` count per each `book`. (Which is what I assume the whole point of the compound key was?)
2. Define both tables as MyISAM and lose the FK constraint.
   This, by the way, is what MySQL did silently on your old server.

[AmiMitra]

yes u got it right...i want a seperate resv_id count for each book...but what is very surprising to me is that...i have created both of the tables in one machine. and it gave out no error. then i simply saved the queries and then copy pasted in another machine, where it is giving this error. how come that is possible? and obviously i have downloaded mysql from the same site in both the machines.

[Atli]

It's most likely because your first server was set up to use MyISAM as the default storage engine, while your second server was set up to use InnoDB.

Your first servers, using MyISAM as a default, would simply drop the FK constraint in your second table, which would then be created without an error.

Your second server, using InnoDB as a default, would try to keep the FK constraint, giving you the 1005 error.

The problem was always there, but the first server just didn't have to check for it.

[AmiMitra]

ok, that might be the cause. so if i want to continue with the idea that a seperate resv_id count shud be there for each book , i have to go without the foreign key constraint.
thanx a ton for all your concern. this site is really being helpful to me for my project.

[Atli]

Yep, for now at least it seems it has to be either the FK or the independent count.

Just out of interest, why do you want each book to have an independent count?
I can't really think of a good reason why this would be needed.