Mysql Create Error 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
Errno 150 Mysql Foreign Key
about Stack Overflow the company Business Learn more about hiring developers or posting ads mysql can't create table errno 150 foreign key with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow
Error Code: 1005. Can't Create Table (errno: 150)
is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL: Can't create table (errno: 150) up vote 122 down vote favorite 19 mysql error 1005 I am trying to import a .sql file and its failing on creating tables. Here's the query that fails: CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`), CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I exported the .sql from the the same database, I dropped error 1005 (hy000) all the tables and now im trying to import it, why is it failing? MySQL: Can't create table './dbname/data.frm' (errno: 150) sql mysql phpmyadmin mysql-error-1005 share|improve this question edited Oct 31 '10 at 0:37 OMG Ponies 199k37360417 asked Oct 31 '10 at 0:24 gtilx 75921018 1 For essentially all the causes of this error, here is an exhaustive resource for what causes errno 150 (and errno 121/other foreign key errors) in MySQL. –John Smith Sep 29 '12 at 0:47 16 I've found that the columns must be identical (even the unsigned flag must match). –Justin Skiles Dec 19 '12 at 19:54 3 @JohnSmith ... where? –Charles Wood Jul 29 '13 at 17:07 3 I suggest reading this blog post that lists 10 possible causes: verysimple.com/2006/10/22/… –Mark Amery Jan 2 '14 at 13:54 1 @trejder Curses! –Charles Wood Nov 7 '14 at 17:16 | show 2 more comments 27 Answers 27 active oldest votes up vote 134 down vote accepted From the MySQL - FOREIGN KEY Constraints Documentation: 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 correct column names and types, and it must hav
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
Error 1005 (hy000): Can't Create Table (errno: 150)
company Business Learn more about hiring developers or posting ads with us Stack Overflow mysql error 1005 can't create table Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2
Can T Create Table Errno 121
million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Errno 150 up vote 18 down vote favorite 4 I'm creating a few simple tables and I can't http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 get passed this foreign key error and I'm not sure why. Here's the script below. create TABLE Instructors ( ID varchar(10), First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, PRIMARY KEY (ID) ); create table Courses ( Course_Code varchar(10), Title varchar(50) NOT NULL, PRIMARY KEY (Course_Code) ); create table Sections ( Index_No int, Course_Code varchar(10), Instructor_ID varchar(10), PRIMARY KEY (Index_No), FOREIGN KEY (Course_Code) REFERENCES Courses(Course_Code) ON DELETE cascade ON UPDATE cascade, FOREIGN KEY http://stackoverflow.com/questions/16227199/mysql-errno-150 (Instructor_ID) REFERENCES Instructors(ID) ON DELETE set default ); Error Code: 1005. Can't create table '336_project.sections' (errno: 150) My data types seem identical and the syntax seems correct. Can anyone point out what I'm not seeing here? Thank you! I'm using MySQL Workbench 5.2 mysql table foreign-keys mysql-workbench share|improve this question asked Apr 26 '13 at 1:13 Sixers17 1771211 add a comment| 8 Answers 8 active oldest votes up vote 22 down vote accepted If you're using the InnoDB engine, the ON DELETE SET DEFAULT is your problem. Here's an excerpt from the manual: While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables. You can use ON DELETE CASCADE or ON DELETE SET NULL, but not ON DELETE SET DEFAULT. There's more information here. share|improve this answer edited Apr 26 '13 at 1:46 answered Apr 26 '13 at 1:28 Ed Gibbs 18.1k11945 Hi, thanks for your quick response. I tried changing to SET NULL and it still doesn't work. I even removed all of the ON UPDATE/DELETE lines and still gives me the error. Should I be specifying a particular engine to use? –Sixers17 Apr 26 '13 at 1:54 1 Disregard the
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 and http://www.eliacom.com/mysql-gui-wp-errno-150.php 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 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 helpful errors create table (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 Eliacom's MySQL GUI t create table 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 error. This all might depend on the