Error Creating Foreign Key Check Data Types User_id
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 error creating foreign key check data types phpmyadmin Stack Overflow the company Business Learn more about hiring developers or posting ads with
Error Creating Foreign Key On Name (check Data Types)
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is #1452 - cannot add or update a child row: a foreign key constraint fails a community of 4.7 million 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 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` ( `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,15943373 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 si
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 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error creating foreign key up vote 0 down vote favorite I have been searching http://stackoverflow.com/questions/11926324/foreign-key-constrain-fails-with-error-creating-foreign-key-on-table-check-d both SO and Google for this issue, but what i came up with was pretty much what my error message tells me and that's not the problem. I am using PHPMyAdmin to create relations between tables. I have this table "warehouse_product_history" with a column "from" of type int(11) NULL. I try to create a foreign key to the table "warehouses", and the query that PHPMyAdmin executes when doing so is http://stackoverflow.com/questions/14801458/error-creating-foreign-key the following ALTER TABLE `warehouse_product_history` ADD FOREIGN KEY (`from`) REFERENCES `my_db`.`warehouses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; The type of warehouses->id is int(11) NOT NULL, so that shouldn't be the problem. I've tried to change the datatype of the `from-column to int(11) NOT NULL but with the same result. I get this error message Error creating foreign key on from (check data types) mysql share|improve this question edited Feb 10 '13 at 19:41 asked Feb 10 '13 at 19:05 Anton Gildebrand 1,37063161 What specific error are you receiving? Are you receiving this on an existing populated table? Are both tables InnoDB? Have you run SHOW INNODB STATUS after trying to create the constraint? Are both columns identical? –Kermit Feb 10 '13 at 19:08 Updated the question with the error. The warehouse table is populated. warehouse_product_history isn't. I don't have permissions to show innodb status unfortunately. They are indeed identical. –Anton Gildebrand Feb 10 '13 at 19:50 Can you share the table schema for both, so that we can try? –SparKot Feb 10 '13 at 20:34 The tables can be found on this link pastebin.com/pTxg8Bzm –Anton Gildebrand Feb 10 '13 at 20:42 add a comment| 1 Answer 1 active oldest vot
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 http://www.sitepoint.com/forums/showthread.php?622949-Error-creating-foreign-key-on-gender-(check-data-types) content. You can find out more information about the move and how to open a http://forums.devshed.com/mysql-help-4/setting-foreign-keys-520976.html 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 foreign key 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) error creating foreign 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 in
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web Developer Tools Advanced Search Forum Databases MySQL Help Setting up Foreign keys Thread: Setting up Foreign keys Page 1 of 2 12 Last Jump to page: Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread April 3rd, 2008,06:46 PM #1 msteudel View Profile View Forum Posts Visit Homepage Contributing User Devshed Novice (500 - 999 posts) Join Date Sep 2002 Location Seattle, U.S.A. Posts 715 Rep Power 15 Setting up Foreign keys Hi All, New to Foreign key stuff starting a new project and thought I would give it a whirl. I have two tables right now: users =========== id: int(10) email: varchar(255) cases =========== id: int(10) user_id: int(10) I want to create a foreign key from cases.user_id to the users.id. Both tables are InnoDB and I have created a index on user_id and I don't have any data in my tables. This is the code used to create the index: Code: ALTER TABLE `student_cases` ADD INDEX ( `user_id` ) WHen I try and create a foreign key using: Code: ALTER TABLE `student_cases` ADD FOREIGN KEY ( `user_id` ) REFERENCES `yah`.`users` ( `id` ) ON DELETE CASCADE ; I get the following error: Code: Error creating foreign key (check data types) : user_id * Note I'm using phpMyAdmin Thanks, Mark Faq Reply With Quote April 3rd, 2008,09:43 PM #2 r937 View Profile View Forum Posts Visit Homepage SQL Consultant Devshed Supreme Being (6500+ posts) Twitter Join Date Feb 2003 Location Toronto Canada Posts 27,591 Rep Power 4287 give us a hint the error message tells you it's a problem with the datatypes, but you haven't told us what the datatypes are rudy.ca | @rudydotca Buy my SitePoint book: Simply SQL Faq Reply With Quote April 4th, 2008,01:06 AM #3 msteudel View Profile View Forum Posts Visit Homepage Contributing User Devshed Novice (500 - 999 posts) Join Date Sep 2002 Location Seattle, U.S.A. Posts 715 Rep Power 15 Originally Posted by r937 give us a hint the error message tells you it's a problem with the datatypes, but you haven't told us what the datatypes are Heya, Maybe I don't know what data types are ... I take it it's not like integer or varchar ... Faq Reply With Quote April 4th, 2008,04:18 AM #4 pabloj View Profile View Forum Posts Visit Hom