Mysql Error Number 150
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn errno 150 mysql foreign key more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Mysql Error 1005
Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you,
Mysql Error 1005 (hy000)
helping each other. Join them; it only takes a minute: Sign up MySql Error 150 - Foreign keys up vote 13 down vote favorite 2 When I execute the follow two queries (I have stripped them down to
Mysql Can't Create Table Errno 150 Foreign Key
absolutely necessary): mysql> CREATE TABLE foo(id INT PRIMARY KEY); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE bar ( id INT, ref INT, FOREIGN KEY (ref) REFERENCES foo(id)) ENGINE InnoDB; I get the following error: ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150) Where the **** is my error? I haven't found him while staring at this for half an hour. mysql mysql-error-1005 share|improve this question edited Apr 30 '11 at 17:18 OMG error 121 in mysql Ponies 199k37360417 asked May 5 '09 at 15:14 Martin Thurau 4,73842561 add a comment| 7 Answers 7 active oldest votes up vote 25 down vote accepted From FOREIGN KEY Constraints If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message. My suspicion is that it's because you didn't create foo as InnoDB, as everything else looks OK. Edit: from the same page - Both tables must be InnoDB tables and they must not be TEMPORARY tables. share|improve this answer answered May 5 '09 at 15:22 Greg 199k35307303 A bit of quick testing, creating a foo as above with MyISAM, and a bar with InnoDB, suggests that that's the case - try double-checking your table type for foo. –Rob May 5 '09 at 15:26 +1 for "Both tables must be InnoDB tables", thanks a lot! –sanbhat Mar 4 '13 at 11:00 1 Even when both tables were InnoDB, I still had the same problem. I fixed it by created the index on the referred table before creating the foreign key. &ndas
here for a quick overview of the site Help Center Detailed answers to any questions you might error code: 1005. can't create table (errno: 150) have Meta Discuss the workings and policies of this site About error 150 in mysql alter table Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads error 1005 (hy000): can't create table (errno: 150) with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Creating tables with Foreign Keys giving errno: 150 up vote 78 down vote favorite 38 I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 am getting an errno: 150 error and it will not create the table. Here is the SQL for all 3 tables: CREATE TABLE role_groups ( `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`, `name` varchar(20), `description` varchar(200), PRIMARY KEY (`role_group_id`) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `roles` ( `role_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50), `description` varchar(200), PRIMARY KEY (`role_id`) ) ENGINE=InnoDB; create table role_map ( `role_map_id` int not null `auto_increment`, `role_id` int not null, `role_group_id` int not null, primary key(`role_map_id`), foreign key(`role_id`) references roles(`role_id`), foreign key(`role_group_id`) references role_groups(`role_group_id`) ) engine=InnoDB; Any help would be greatly appreciated. mysql foreign-keys errno share|improve this question edited Sep 21 '09 at 23:37 Bill Karwin 283k50395569 asked Sep 21 '09 at 22:55 user176842 1 Could you post the error output and tell us which command (of the three) is causing the error? –dave Sep 21 '09 at 23:08 4 What's with the back-ticks around auto_increment? That's not valid. Auto_increment is a keyword, not an identifier. –Bill Karwin S
SQL Insertion Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database. MySQL Foreign Keys Learn all about MySQL foreign keys in this white paper. MySQL Foreign Key Errors http://www.eliacom.com/mysql-gui-wp-errno-150.php and Errno: 150 Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper. MySQL/SQL Data Validation (with PHP) Learn the importance of data validation in web applications when information is accepted https://www.troyfawkes.com/solved-mysql-error-1005-cant-create-table-errno-150/ from third parties, or even from internal users. MySQL Foreign Key Errors: errno 150, errno 121, and others Diagnosing Errors SHOW ENGINE INNODB STATUS is Your New Best Friend: Click for solutionIf you get one of the really mysql error helpful errors (sarcasm) like the errno 150 or errno 121, then by simply typing in SHOW ENGINE INNODB STATUS, there is a section called "LATEST FOREIGN KEY ERROR". Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. What's the catch?You need SUPER privileges to run it, so if you don't have that, you'll just have to test out the following scenarios. Use can't create table Eliacom's MySQL GUI tool to catch most errors: Click for solutionYou can either download the MySQL GUI to install on your own server, or you can use our free online demo of the MySQL GUI to do it. You can check out our video on how to create foreign keys and indexes using Eliacom's MySQL GUI tool.. MySQL errno 150 ERROR 1005 (HY000): Can't create table 'table' (errno: 150) ERROR 1025 (HY000): Error on rename of 'table' to 'newtable' (errno: 150) Causes and Solutions for errno 150 Data Types Don't Match: Click for solutionThe types of the columns have to be the same (usually). This is one of the most common reasons for errno 150. For instance, if the type of the child column is VARCHAR(50), the type of the parent column should be exactly VARCHAR(50) (since they're supposed to hold the same data). For numeric types, if one is UNSIGNED, then both have to be UNSIGNED. They should match exactly!. I have run into circumstances where it has let me create a foreign key where the child column was a VARCHAR(50) and the parent column was a VARCHAR(200). Interestingly, if I tried to do the opposite for the same tables, reference a child column that was a VARCHAR(200) to a parent column that was a VARCHAR(50), it threw the errno 150 er
Communication Skills Training Interpersonal Skills Training Blog About Blog Troy Fawkes / Archives / Solved: MySQL ERROR 1005: Can't create table (errno: 150) (Foreign Key) Nov 27 Solved: MySQL ERROR 1005: Can't create table (errno: 150) (Foreign Key) November 27, 2011 Troy Fawkes 8 Comments Archives Share on Facebook Share 0 Share on TwitterTweet 0 Share on Google Plus Share 0 Share on Pinterest Share 0 Share on LinkedIn Share 0 This is another stupid error. It has to do with trying to successfully set foreign keys in MySQL. ERROR 1005: Can't create table (errno: 150) Great, that's fantastic. Here's an example of where this error will occur. CREATE TABLE main(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
);
CREATE TABLE other(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
main_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(main_id) REFERENCES main(id)
); So I'm trying to make the table "other" reference the table "main" through the foreign key "main_id" and, if you try it, it'll throw an Error 150. Want the solution? The foreign key "main_id" has to have the exact same type as the primary key that it references. In the example, "main_id" in the table "other" has the type INT NOT NULL while "id" in the table "main" has the type "INT UNSIGNED NOT NULL" and also AUTO_INCREMENT, but that isn't something we have to worry about. To make things incredibly clear, here's the working example. CREATE TABLE main( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) ); CREATE TABLE other( id INT UNSIGNED NOT NULL AUTO_INCREMENT, main_id INT UNSIGNED NOT NULL, PRIMARY KEY(id), FOREIGN KEY(main_id) REFERENCES main(id) ); To solve ‘MySQL ERROR 1005: Can't create table (errno: 150)‘ you likely just have to ensure that your foreign key has the exact same type as the primary key. Hope it helps. Share on Facebook Share 0 Share on TwitterTweet 0 Share on Google Plus Share 0 Share on Pinterest Share 0 Share on LinkedIn Share 0 Facebook Twitter Tumblr Pinterest Google+ LinkedIn E-Mail About The Author My name is Troy Boileau but I go by Troy Fawkes. I'm a digital marketer working in Toronto. On top of that, my passions include social skills, networking and dating; subjects that I've been semi-professionally involved with for