Error Creating Foreign Key Check Data Types Mysql
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 company Business Learn more about hiring creating foreign key in mysql workbench developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask creating primary key mysql 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 creating foreign key sql server 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 creating foreign key phpmyadmin 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`) )
Error Creating Foreign Key Check Data Types Phpmyadmin
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 signed or unsigned. You may need to OPTIMIZE your tables after changing the data types to match. share|improve this answer edited Jul 3 at 14:18 alexw 2,0971926 answered Aug 12 '12 at 22:26 Mihai Stancu 11.2k11638 Right. I have mistakenly changed the data type of hotels.id to match cities.id wit
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
Error Creating Foreign Key On Name (check Data Types)
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation #1452 - cannot add or update a child row: a foreign key constraint fails 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 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 site About Us http://stackoverflow.com/questions/7762237/phpmyadmin-cant-set-specific-foreign-key-even-though-types-are-the-same 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 http://forums.devshed.com/mysql-help-4/setting-foreign-keys-520976.html 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 phpMyAdmin: can't set specific foreign key, even though foreign key types are the same up vote 1 down vote favorite My list_items table has the following columns `ListID` `WordID` My lists table has a primary, auto-incremented primary key `ListID`. I want to make the `ListID` in list_items a foreign key referencing the primary key in lists, but I get an error: Error creating foreign key on `ListID` (check data types). Both columns are int(8), and both table creating foreign key types are InnoDB. What could possibly be the issue? Following these instructions has worked fine in setting up other foreign keys. The only thing I can think of is on phpMyAdmin's main screen, there's a message: "The additional features for working with linked tables have been deactivated. To find out why click here." But wouldn't any issue there prevent me from setting up other foreign keys? Relevant SQL: -- -- Table structure for table `lists` -- CREATE TABLE IF NOT EXISTS `lists` ( ```ListID``` int(8) NOT NULL AUTO_INCREMENT, ```UserID``` int(8) NOT NULL, ```privacy``` varchar(25) NOT NULL, ```name``` varchar(50) NOT NULL, ```description``` text NOT NULL, ```date created``` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ```date modified``` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (```ListID```), KEY ```UserID``` (```UserID```) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `lists` -- -- -- Table structure for table `list_items` -- CREATE TABLE IF NOT EXISTS `list_items` ( ```ListID``` int(8) NOT NULL, ```WordID``` int(8) NOT NULL, KEY ```ListID``` (```ListID```), KEY ```WordID``` (```WordID```) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `list_items` -- -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( ```UserID``` int(8) NOT NULL AU
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