Mysql Error Creating Foreign Key Check Data Types
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 developers error creating foreign key check data types phpmyadmin or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Error Creating Foreign Key On Name (check Data Types)
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
#1452 - Cannot Add Or Update A Child Row: A Foreign Key Constraint Fails
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,19143373 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,14311128 answered Aug 12 '12 at 22:26 Mihai Stancu 11.2k11639 Right. I have mistakenly changed the data type of hotels.id to match cities.id without realizing that the relationship is in hotels.city. Thank you
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 http://stackoverflow.com/questions/11926324/foreign-key-constrain-fails-with-error-creating-foreign-key-on-table-check-d 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 MySQL Error creating foreign key on
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/33717930/error-creating-foreign-key-check-data-types Meta Discuss the workings and policies of this site About Us http://dba.stackexchange.com/questions/142646/error-creating-foreign-key-on-check-data-types 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 foreign key like you, helping each other. Join them; it only takes a minute: Sign up Error creating foreign key (check data types) up vote 1 down vote favorite I'm trying to create a relation between two tables. And here is the query for each table and the foreign key creation, CREATE TABLE IF NOT EXISTS `quotes` ( `id` error creating foreign int(10) unsigned NOT NULL AUTO_INCREMENT, `quote` text COLLATE utf8_unicode_ci NOT NULL, `author` int(11) NOT NULL, `topic` int(11) NOT NULL, `language` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `author` (`author`), KEY `topic` (`topic`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `authors` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `author` int(11) NOT NULL, `period` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `photo` text COLLATE utf8_unicode_ci NOT NULL, `references` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `author` (`author`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; ALTER TABLE `quotes` ADD FOREIGN KEY ( `author` ) REFERENCES `mytestdb`.`authors` ( `id` ) ON DELETE RESTRICT ON UPDATE RESTRICT ; But it is throwing the following error, Error creating foreign key on author (check data types) I'm not sure what this error about. Any help on this would be greatly appre
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 Page Not Found This question was removed from Database Administrators Stack Exchange for reasons of moderation. Please refer to the help center for possible explanations why a question might be removed. Here are some similar questions that might be relevant: Lost tables in Drupal site database Foreign Key Constraint fails Cannot create FOREIGN KEY for a table importing database backup to phpmyadmin return #1064 #1046: No database selected in phpMyAdmin Trying to recover/import/transfer database from MySQL data dir Error Creating Foreign Key in Oracle Creating table with foreign key and index using sqlcmd.exe and batch file When create trigger then after error shown in inserting the values Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables Try a Google Search Try searching for similar questions Browse our recent questions Browse our popular tags If you feel something is missing that should be here, contact us. about us tour help blog chat data legal privacy policy work here advertising info mobile contact us feedback Technology Life / Arts Culture / Recreation Science Other Stack Overflow Server Fault Super User Web Applications Ask Ubuntu Webmasters Game Development TeX - LaTeX Software Engineering Unix & Linux Ask Different (Apple) WordPress Development Geographic Information Systems Electrical Engineering Android Enthusiasts Information Security Database Administrators Drupal Answers SharePoint User Experience Mathematica Salesforce ExpressionEngine® Answers Cryptography Code Review Magento Signal Processing Raspberry Pi Programming Puzzles & Code Golf more (7) Photography Science Fiction & Fantasy Graphic Design Movies & TV Music: Practice & Theory Seasoned Advice (cooking) Home Improvement Personal Finance & Money Academia more (8) English Language & Usage Skeptics Mi Yodeya (Judaism) Travel Christianity English Language Learners Japanese Language Arqade (gaming) B