Mysql Error 150 Cannot Resolve Table Name Close To
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 more about hiring developers or posting ads 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 programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Cannot resolve table name close to up vote 6 down vote favorite 1 I want to create a references to foreign table. but i'm getting the following error: query: CREATE TABLE category_ids (id INT, post_id INT, INDEX par_ind (post_id), FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE ) ENGINE=INNODB; SHOW ENGINE INNODB STATUS\G: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2013-08-23 00:11:06 7f6f49e7b700 Error in foreign key constraint of table fun/category_ids: FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE ) ENGINE=INNODB: Cannot resolve table name close to: (id) ON DELETE CASCADE ) ENGINE=INNODB posts table structure mysql> describe post; +-------------+-----------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | ... +-------------+-----------------------+------+-----+---------------------+----------------+ 22 rows in set (0.00 sec) mysql sql reference share|improve this question asked Aug 22 '13 at 21:20 user2696962 143312 Possibly it thinks "post" is a reserved word, what if you qualified the post in your foreign key with b
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 more about hiring developers or posting ads 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 http://stackoverflow.com/questions/18391034/cannot-resolve-table-name-close-to million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Foreign Key - Cannot Resolve Table Name Close TO up vote 0 down vote favorite I've run into a foreign key issue that really has me stumped. The specific error I get when running my migration is: http://stackoverflow.com/questions/35568053/mysql-foreign-key-cannot-resolve-table-name-close-to SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table products add constraint products_provider_id_foreign foreign k ey (provider_id) references id (providers) on delete cascade) The MySQL SHOW ENGINE INNODB STATUS; result under Latest Foreign Key Error is: 2016-02-22 21:38:17 11debc000 Error in foreign key constraint of table testdb/#sql-186_18db: foreign key (provider_id) references id (providers) on delete cascade: Cannot resolve table name close to: (providers) on delete cascade I've checked all of the following: Same data type: int Same data length: 10 Same signing: unsigned The target table is a key: primary Both tables correct engine: INNODB Both tables same charset: utf8 Both tables same collation: utf8 The first table is providers and has a primary key id defined as INT(10) and unsigned: The second table is products and has a column called provider_id defined as INT(10) and unsigned. This error is of course well documented, especially on SO, but none of the common things (including this checklist) are working. Does a
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 http://stackoverflow.com/questions/5888710/error-cant-create-table-on-adding-foreign-key the company Business Learn more about hiring developers or posting ads with us Stack http://dba.stackexchange.com/questions/133342/cannot-resolve-table-name-error-when-applying-mysql-dump-to-rds 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 programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error “Can't create table…” on adding FOREIGN KEY up vote 8 down vote favorite I have created the mysql error table by script: SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; DROP TABLE IF EXISTS `Table1`; CREATE TABLE IF NOT EXISTS `Table1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `parentId` bigint(20) DEFAULT NULL, `name` varchar(1024) NOT NULL, `description` varchar(16384) NOT NULL DEFAULT '', `imageId` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`(255)), KEY `parentId` (`parentId`), KEY `imageId` (`imageId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ; INSERT INTO `Table1` (`id`, `parentId`, `name`, `description`, `imageId`) VALUES (0, NULL, 'name1', '', NULL), mysql error 150 (12, 0, 'name2', '', NULL); Then I try to add foreign key: ALTER TABLE `Table1` ADD CONSTRAINT `Table1_ibfk_2` FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`); And obtain the following error: ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150) What is wrong? I run SHOW ENGINE INNODB STATUS; There LATEST FOREIGN KEY ERROR follows: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 110504 22:06:55 Error in foreign key constraint of table sandbox/#sql-c28_61: FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`): Cannot resolve table name close to: (`id`) ------------ But it does not help me to realize what is wrong. I use Windows Vista, MySql 5.5.11 UPDATE: The issue appears when I upgrade from MySql 5.0.67. mysql foreign-keys share|improve this question edited May 4 '11 at 19:38 asked May 4 '11 at 19:17 sergtk 5,13654795 add a comment| 5 Answers 5 active oldest votes up vote 4 down vote accepted Background ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150) When you command MySQL to ALTER TABLE it really does the following steps: copy the data from the existing table to a new temporary table. Alter the structure of the new temporary table delete the old table rename the temporary table to the old_table_name. Problem Your request is failing in step 1. Note that tablenames link to files. On linux table
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 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 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 Anybody can answer The best answers are voted up and rise to the top “Cannot resolve table name” error when applying MySQL dump to RDS up vote 3 down vote favorite I'm setting up replication of an external MySQL instance to RDS. Both the external MySQL instance and RDS run MySQL 5.7.10. I've followed the procedure, but when dumping the database with mysqldump and sending it to mysql connected to RDS, I get the following error: ERROR 1215 (HY000) at line 2081: Cannot add foreign key constraint SHOW ENGINE InnoDB STATUS on RDS shows: ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2016-03-25 01:38:56 0x2ad07ddcf700 Error in foreign key constraint of table db/Prospect: FOREIGN KEY (`restaurantId`) REFERENCES `Restaurant` (`id`), CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci: Cannot resolve table name close to: (`id`), CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci OK, indeed the dump file recreates the tables alphabetically, so the Prospect table appears before Restaurant and Zone, which can explain the error. But NO, because mysqldump adds SET FOREIGN_KEY_CHECKS=0 to the top of the file, so it should work anyway. So I decided to test it against a vanilla MySQL 5.7 installation, and no surprise, the dump is imported successfully with the exact same commands. What causes this error specifically on RDS