Mysql Cannot Create Table Error No 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 mysql can't create table errno 150 foreign key more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Errno 150 Mysql Foreign Key
Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like can't create table (errno: 121) you, helping each other. Join them; it only takes a minute: Sign up Mysql. Can't create table errno 150 up vote 66 down vote favorite 7 I have to create a db with 2 tables in mysql but mysql error 1005 can't create table the script fails with errno 150 (Foreign key problem). I double-checked the foreign key fields to be the same on both tables and I can't find any error. Here is the script: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; DROP SCHEMA IF EXISTS `testdb`; CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; USE `testdb`; DROP TABLE IF EXISTS `testdb`.`table1` ; CREATE TABLE IF NOT EXISTS `testdb`.`table1` ( `id` INT
Error 1005 (hy000) Can't Create Table (errno 150)
UNSIGNED NOT NULL , `field1` VARCHAR(50) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; DROP TABLE IF EXISTS `testdb`.`table2` ; CREATE TABLE IF NOT EXISTS `testdb`.`table2` ( `id` INT NOT NULL AUTO_INCREMENT , `field1` VARCHAR(50) NULL , `date` DATE NULL , `cnt` INT NULL , PRIMARY KEY (`id`) , INDEX `FK_table2_table1` (`field1` ASC) , CONSTRAINT `FK_table2_table1` FOREIGN KEY (`field1`) REFERENCES `testdb`.`table1` (`field1` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; I've tried it in Windows and Ubuntu with different versions of Mysql and didn't work. Any ideas? Thanks a lot. mysql mysql-error-1005 share|improve this question edited Apr 30 '11 at 17:14 OMG Ponies 199k37360417 asked Nov 17 '09 at 14:40 David Espart 6,05662439 9 I also received this error by attempting to create a table with a FK constraint to another table that did not yet exist. It's an easy problem to fix, made very difficult by a completely useless error message. –Cerin Mar 12 '12 at 16:08 2 For those getting here from google, this error can also happen if you make a typo on the foreign key's table name. –Dave C Nov 26 '13 at 17:39 add a comment| 21 Answers 21 active oldest votes up vote 54 down vote accepted table1.field1 has no index defined on it. It
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 can t create table errno 150 django about MySQL foreign keys in this white paper. MySQL Foreign Key Errors and can't create table (errno 150) foreign key constraint is incorrectly formed Errno: 150 Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper. MySQL/SQL
Mysql Errno 150 Alter Table
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, http://stackoverflow.com/questions/1749332/mysql-cant-create-table-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 (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, http://www.eliacom.com/mysql-gui-wp-errno-150.php 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 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, the
log in tour help Tour Start 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 http://dba.stackexchange.com/questions/80435/mysql-create-table-shows-error-1005-errno-150 about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site https://github.com/goalio/GoalioRememberMe/issues/26 for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question create table Anybody can answer The best answers are voted up and rise to the top Mysql Create table shows ERROR 1005 errno 150 up vote 0 down vote favorite I am trying to make a junction table, but I getting a ERROR 1005 errno 150 CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage VARCHAR(10), evolvesFrom VARCHAR(12), HP INT, retreatCost INT, weakness VARCHAR(10), weaknessAmount VARCHAR(5), resistance t create table VARCHAR(5), resistanceAmount VARCHAR(5) ); CREATE TABLE cardTags ( tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY, tagName VARCHAR(60) NOT NULL ); CREATE TABLE 61furiousFistPokemonTags ( tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY, pokemonCardNumber int, tagName VARCHAR(60), FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber), FOREIGN KEY (tagName) REFERENCES cardTags(tagName) ); Any help would be greatly appreciated. mysql innodb foreign-key table share|improve this question edited Oct 17 '14 at 15:23 RolandoMySQLDBA 108k15139276 asked Oct 17 '14 at 3:43 user50479 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName). share|improve this answer answered Oct 17 '14 at 8:13 Nawaz Sohail 611314 Although you did not provide a solution, +1 for revealing root cause. –RolandoMySQLDBA Oct 17 '14 at 15:19 add a comment| up vote 0 down vote Very often it happens, when the foreign key and the reference key don't have same type or same length. Click here for more details. share|improve this answer answered Oct 17 '14 at 7:30 Musakkhir Sayyed 240314 add a comment| up vote 0 down vote Here is the quick fix: Just add a UNIQUE KEY on tagName in the cardTags tabl
Sign in Pricing Blog Support Search GitHub This repository Watch 9 Star 29 Fork 17 goalio/GoalioRememberMe Code Issues 12 Pull requests 3 Projects 0 Pulse Graphs New issue MySQL: Can't create table (errno: 150) when running schema.sql #26 Open webdevilopers opened this Issue May 12, 2014 · 7 comments Projects None yet Labels None yet Milestone No milestone Assignees No one assigned 3 participants webdevilopers commented May 12, 2014 When running the schema.sql https://github.com/goalio/GoalioRememberMe/blob/master/data/schema.sql in phpMyAdmin it successfully creates the table user_remember_me but the ALTER command ends with the following error: MySQL: Can't create table (errno: 150) According to MySQL doc this means: 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 table user and its column user_idexists. Is this a privilege problem or a conflict with the ZfcUser structure? stijnhau commented May 12, 2014 i have tested it and i diodn't get an error. But what happens if you change the type of user_if from INT(11) to INT(10) in the remember_me table. And afterwards run the ALTER query? webdevilopers commented May 12, 2014 Thanks for the quick answer, @stijnhau . I tried your attempt but it didn't help. I then tried to create the relation manually in phpMyAdmin: ALTER TABLE `user_remember_me` ADD FOREIGN KEY ( `user_id` ) REFERENCES `plusquam2_development`.`user` ( `user_id` ) ON DELETE CASCADE ON UPDATE CASCADE ; The error then returned told me to check the column types. I then removed the UNSIGNED attribute from user_remember_metable. After that the ALTER statement was accepted. I don't know