468,133 Members | 1,374 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

Error 1005 when creating tables on some servers.

i am using mysql 5.1. i have one table named as resv_record. the format is
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
May 21 '09 #1
9 2714
prabirchoudhury
162 100+
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 ...
May 22 '09 #2
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...giving this error... i simply dont know what to do...
May 22 '09 #3
Atli
5,058 Expert 4TB
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.
May 22 '09 #4
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?
May 22 '09 #5
Atli
5,058 Expert 4TB
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.
May 23 '09 #6
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.
May 23 '09 #7
Atli
5,058 Expert 4TB
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.
May 23 '09 #8
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.
May 23 '09 #9
Atli
5,058 Expert 4TB
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.
May 24 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Morten Gulbrandsen | last post: by
reply views Thread by Morten Gulbrandsen | last post: by
3 posts views Thread by dstewart | last post: by
3 posts views Thread by bill_hounslow | last post: by
4 posts views Thread by scottrm | last post: by
1 post views Thread by =?Utf-8?B?VmlqYXkgQ2hpa3Rl?= | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.