Mysql Add Foreign Key Error Code 1005
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 mysql error 1005 can't create table Learn more about Stack Overflow the company Business Learn more about hiring developers or
Mysql Error 1005 Errno 121
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow mysql error 1005 errno 150 Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Foreign Key add fails in MySQL with Error
Error Code 1005 Iphone
Code 1005, number 150 up vote 14 down vote favorite 4 So I'm attempting to add a new foreign key to one of my tables as such: ALTER TABLE `UserTransactions`.`ExpenseBackTransactions` ADD CONSTRAINT `FK_EBTx_CustomAccountID` FOREIGN KEY (`CustomAccountID` ) REFERENCES `UserTransactions`.`CustomAccounts` (`CustomAccountID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD INDEX `FK_EBTx_CustomAccountID` (`CustomAccountID` ASC) ; and I keep getting the following error: Error Code: 1005 Can't create table './UserTransactions/#sql-187a_29.frm' mysql errno 121 (errno: 150) I've done quite a bit of changes in the past to this and other tables, and this is the first time I've run into this issue. Any ideas what is causing it? UPDATE My SHOW INNODB STATUS error: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 110525 15:56:36 Error in foreign key constraint of table UserTransactions/#sql-187a_2c: FOREIGN KEY (`CustomAccountID` ) REFERENCES `UserTransactions`.`CustomAccounts` (`CustomAccountID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `FK_EBTx_CustomAccountID` (`CustomAccountID` ASC): Cannot resolve table name close to: (`CustomAccountID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `FK_EBTx_CustomAccountID` (`CustomAccountID` ASC) mysql foreign-keys mysql-error-1005 share|improve this question edited May 25 '11 at 22:05 asked May 25 '11 at 21:51 thaBadDawg 3,51752443 What does SHOW INNODB STATUS say? Do both columns have the same datatype (and length?)? Is there already another key with that name in the database somewhere? –Konerak May 25 '11 at 21:55 FOREIGN KEY is only supported by InnoDB dev.mysql.com/doc/refman/5.5/en/… . check if this is causing the problem. –Ibrahim Azhar Armar May 25 '11 at 21:57 @Ibrahim: myisam silently ignores foreign key specifications without error. –Marc B May 25 '11 at 21:59
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
Can T Create Table Errno 150
Us Learn more about Stack Overflow the company Business Learn more about hiring
Error 10005
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join errno 150 foreign key constraint is incorrectly formed the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1005. Can't create http://stackoverflow.com/questions/6131421/foreign-key-add-fails-in-mysql-with-error-code-1005-number-150 table '…' (errno: 150) up vote 74 down vote favorite 30 I searched for a solution to this problem on internet and checked the SO questions but no solution worked for my case. I want to create a foreign key from table sira_no to metal_kod. ALTER TABLE sira_no ADD CONSTRAINT METAL_KODU FOREIGN KEY(METAL_KODU) REFERENCES metal_kod(METAL_KODU) ON DELETE SET NULL ON UPDATE SET NULL ; This script http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150 returns: Error Code: 1005. Can't create table 'ebs.#sql-f48_1a3' (errno: 150) I tried adding index to the referenced table: CREATE INDEX METAL_KODU_INDEX ON metal_kod (METAL_KODU); I checked METAL_KODU on both tables (charset and collation). But couldn't find a solution to this problem. Does anyone have any idea? Thanks in advance. EDIT: Here is the metal_kod table: METAL_KODU varchar(4) NO PRI DURUM bit(1) NO METAL_ISMI varchar(30) NO AYAR_YOGUNLUK smallint(6) YES 100 mysql mysql-error-1005 share|improve this question edited Jan 27 '14 at 7:57 Melon 786617 asked Jan 26 '12 at 13:14 lamostreta 85122046 Can you show the schema for the metal_kod table ... which field in that table should the Foreign Key reference ? –ManseUK Jan 26 '12 at 13:28 add a comment| 9 Answers 9 active oldest votes up vote 199 down vote accepted Error Code: 1005 -- there is a wrong primary key reference in your code usually it's due to a reference FK field not exist. might be you have typo mistake,or check case it should be same, or there's a field-type mismatch. FK-linked fields must match definitions exactly. Some Known causes may be : The two key fields type and/or size doesn’t
CI MySQL Sandbox MariaDB data chef Adtech Twitter GitHub RSS Improved Foreign Key Error Messages in MySQL 5.6 It took a little more than 10 years, but as of version http://mechanics.flite.com/blog/2013/11/04/improved-foreign-key-error-messages-in-mysql-5-dot-6/ 5.6 MySQL is finally using error code 1215! Since MySQL first introduced foreign key support more than 10 years ago in version 3.23.44, all the way through version https://www.troyfawkes.com/solved-mysql-error-1005-cant-create-table-errno-150/ 5.5, the server has always given a generic 1005 error when you try to add an invalid foreign key. Here's an example in MySQL 5.5, where the parent key mysql error is unsigned and the child key is signed: 1 2 3 4 5 6 7 8 9 10 mysql> create table parent ( -> id int unsigned primary key -> ) engine = innodb; Query OK, 0 rows affected (0.01 sec) mysql> create table child ( -> id int mysql error 1005 primary key, -> parent_id int, -> foreign key fk_child_to_parent (parent_id) references parent (id) -> ) engine = innodb; ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150) Error 1005 doesn't give me any clue about why the DDL failed. If I run SHOW ENGINE INNODB STATUS and look at the "LATEST FOREIGN KEY ERROR" section I can get more details. But how would I even know to look there based on the generic 1005 error? If I run the same DDL in MySQL 5.6 I get the 1215 error: 1 2 3 4 5 6 7 8 9 10 mysql> create table parent ( -> id int unsigned primary key -> ) engine = innodb; Query OK, 0 rows affected (0.02 sec) mysql> create table child ( -> id int primary key, -> parent_id int, -> foreign key fk_child_to_parent (parent_id) references parent (id) -> ) engine = innodb; 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