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 Learn more about Stack Overflow
Mysql Error 1005 Can't Create Table
the company Business Learn more about hiring developers or posting ads with us Stack Overflow mysql error 1005 errno 121 Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
Mysql Error 1005 Errno 150
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 Code 1005, number 150 up vote 14 down vote favorite 4 error code 1005 iphone 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' (errno: 150) I've done quite a bit of changes in the past to this and error 10005 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 add a comment| 3 Answers 3 active oldest votes up vote 25 down vote accepted There's a nice checklist here. Below is a running list of known causes that people have
here for a quick overview of the site Help Center Detailed answers to any questions you might
Errno 150 Foreign Key Constraint Is Incorrectly Formed
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 developers or posting ads
Error 1005 Archeage
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 4.7 million programmers, http://stackoverflow.com/questions/6131421/foreign-key-add-fails-in-mysql-with-error-code-1005-number-150 just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1005. Can't create 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 http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150 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 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 85122045 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 a
for Developers MariaDB Contributor Agreement MariaDB Contributor Agreement FAQs Community Ambassadors Events Past Events and Conferences Sponsor Sponsors List of Donors Blog About MariaDB https://mariadb.org/mariadb-innodb-foreign-key-constraint-errors/ Sponsors Governance Logos and Badges MariaDB Trademark Usage Statistics Service Providers Maintenance Policy Security Policy Download Learn Get Involved Social Media Getting Started for Developers MariaDB http://www.paneru.com/pan-blog/2013/08/15/error-code-1005-cant-create-table-errno-150/ Contributor Agreement MariaDB Contributor Agreement FAQs Community Ambassadors Events Past Events and Conferences Sponsor Sponsors List of Donors Blog HomeGeneralMariaDB: InnoDB foreign key constraint errors MariaDB: error 1005 InnoDB foreign key constraint errors 2015-08-07 4 Comments Written by Jan Lindstrom Introduction A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is mysql error 1005 called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example: create table parent ( id int not null primary key, name char(80) ) engine=innodb; create table child ( id int not null, name char(80), parent_id int, foreign key(parent_id) references parent(id) ) engine=innodb; As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys: InnoDB (both innodb_plugin and XtraDB) PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/) Solid
is really frustrating to see MySQL error messages ‘ERROR 1005 (HY000): Can't create table ‘dbName.tableName_foo' (errno: 150)' or ‘ERROR 1005 (HY000): Can't create table ‘dbName.tableName_foo' (errno: 121)' or 'ERROR 1005 (HY000): Can't create table ‘dbName.tableName_foo' (errno: 121)' when creating tables or adding foreign keys to the table. I have experienced such errors on MySQL as my agile project keeps dropping/re-creating tables/foreign keys as we need them. I will discuss this here on how to avoid such issues and resolve it if it happens. When we create tables or foreign keys we may encounter such error messages. InnoDB tables stores foreign key constraints for future references hence I would advice to drop foreign Keys and indexes before dropping the table. As precaution, if we only drop tables after dropping all foreign keys and indexes of the table first, we may never encounter such problems. Normally the error occurs when we create new table with the same name that existed in past in the database which had foreign keys or other indexes that you creating. As InnoDB references indicates different keys for the constraints it throws an error. Sometime the solution could be as simple as correcting typos on your column names of referenced table. While others could be lot worse. If have knowledge of past table with the same name try to create them with all identical structure including key names for your constraints. Then you can drop all the foreign key and create new key with different key names and references. If you do not need InnonDB tables you can create MyISAM tables as a solution. MyISAM table seems to be more tolerant towards constraints keys. MySQL clearly indicates following on it site: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It