Error When Creating Foreign Key
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 error creating foreign key (check data types) company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions creating foreign key mysql Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million creating foreign key access programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Foreign Key Constrain Fails with “Error creating foreign key on [table] (check data types)” up vote 4 down vote favorite The
Creating Foreign Key Sql Server
following query fails with error "Error creating foreign key on city (check data types)": ALTER TABLE `hotels` ADD FOREIGN KEY ( `city` ) REFERENCES `mydatabase`.`cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; Basically I want to have a ony-to-many relation between city.id and hotels.city. Here are both tables: CREATE TABLE IF NOT EXISTS `cities` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `hotels` ( creating foreign key in mysql workbench `id` int(11) NOT NULL AUTO_INCREMENT, `city` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ) ENGINE=InnoDB; mysql share|improve this question edited Aug 12 '12 at 22:29 asked Aug 12 '12 at 22:23 hasMobi - Android Apps 3,19443373 Do the tables already have data in them? –Tieson T. Aug 12 '12 at 22:25 Hotels is empty, City has some rows that are already constrained with another table (restaurants) in the same way I am trying for hotels above. It worked the first time, but I built the query by hand, without using phpMyAdmin. –hasMobi - Android Apps Aug 12 '12 at 22:26 One is INT, the other BIGINT. –ypercubeᵀᴹ Aug 12 '12 at 22:26 Did you read the error message? It contains the info to solve the problem. –Pred Oct 22 '15 at 9:37 add a comment| 3 Answers 3 active oldest votes up vote 14 down vote accepted The data types need to match: cities.id int(11) hotels.city bigint(20) Needs to become either: cities.id bigint(20) hotels.city bigint(20) Or: cities.id int(11) hotels.city int(11) Depending on what your application needs. Also worth mentioning is the fact that both need to be either signed or unsigned. You may need to OPTIMIZE your tables after changing the data types to match. share|improve this answer ed
here for a quick overview of the site Help Center Detailed answers to any questions you might have creating foreign key in oracle Meta Discuss the workings and policies of this site About Us
Creating Foreign Key In Sql Server 2012
Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads
Creating A Foreign Key In Access 2013
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/11926324/foreign-key-constrain-fails-with-error-creating-foreign-key-on-table-check-d just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Error creating foreign key on
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 http://stackoverflow.com/questions/16866318/adding-foreign-key-fails-in-mysql-errno-150 site About Us Learn more about Stack Overflow the company Business Learn http://www.sitepoint.com/forums/showthread.php?622949-Error-creating-foreign-key-on-gender-(check-data-types) 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 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Adding foreign key Foreign Key Fails in MySQL (errno 150) up vote 5 down vote favorite I've read many other posts about receiving MySQL errno 150 when trying to add a foreign key copnstraint, however I haven't found a solution yet. I hope I'm not doing something stupid. I made a simple test case. Both tables are InnoDB. Both tables are UTF-8. Both columns are int(11) unsigned creating foreign key (making color_id NOT NULL doesn't make a difference). (EDIT: I WAS WRONG ABOUT THIS, AND THIS WAS THE SOLUTION) Here are my two tables: Table widgets: CREATE TABLE `widgets` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL DEFAULT '', `color_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Table colors: CREATE TABLE `colors` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; I just created these tables, there is no content. When I try to add a foreign key constraint to link widgets.color_id to colors.id, this happens: mysql> ALTER TABLE `widgets` ADD FOREIGN KEY (`color_id`) REFERENCES `color` (`id`); ERROR 1005 (HY000): Can't create table 'production.#sql-7b1_2dd7' (errno: 150) I'll just add that I haven't been able to use my GUI tool of choice -- Sequel Pro on OSX -- either. I get the same error message when trying to create a foreign key relationship. SHOW ENGINE INNDODB STATUS returns this: 130531 17:23:06 Error in foreign key constraint of table production/#sql-7b1_2c80: FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`): Cannot find an index in the referenced table wher
MySQL Error creating foreign key on gender (check data types) The SitePoint Forums have moved. You can now find them here. This forum is now closed to new posts, but you can browse existing content. You can find out more information about the move and how to open a new account (if necessary) here. If you get stuck you can get support by emailing forums@sitepoint.com If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. SitePoint Sponsor User Tag List Results 1 to 5 of 5 Thread: Error creating foreign key on gender (check data types) Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jun 20, 2009,12:45 #1 qazwsxedc View Profile View Forum Posts SitePoint Member Join Date Jan 2008 Posts 16 Mentioned 0 Post(s) Tagged 0 Thread(s) Error creating foreign key on gender (check data types) Hi there, wonder if anyone has had this problem? I'm getting this error message - Error creating foreign key on gender (check data types) even though the data types of the fields I'm trying to create a constraint between are identical - int(2) unsigned. Hope someone can stop me tearing out what little hair I have left... Jun 20, 2009,13:14 #2 qazwsxedc View Profile View Forum Posts SitePoint Member Join Date Jan 2008 Posts 16 Mentioned 0 Post(s) Tagged 0 Thread(s) Never mind, recreated the tables from scratch and it's working now for some reason. Jun 20, 2009,15:37 #3 r937 View Profile View Forum Posts SQL Consultant Join Date Jul 2002 Location Toronto, Canada Posts 39,347 Mentioned 63 Post(s) Tagged 3 Thread(s) how many different values of gender are you expecting? INTEGER UNSIGNED gives you just over 4 billion, i hope that's enough ... rudy.ca | @rudydotca Buy my SitePoint book: Simply SQL "giving out my real stuffs" Jun 21, 2009,05:55 #4 qazwsxedc View Profile View Forum Posts SitePoint Member Join Date Jan 2008 Posts 16 Mentioned 0 Post(s) Tagged 0 Thread(s) Ha, yeah it just might be... would it cause any problems though? I thought that with it being int(2) it would only allow 2 digits at most? It's an adult contacts site - Code