Error Creating Foreign Key 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 creating foreign key in mysql workbench about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Create Foreign Key Mysql Phpmyadmin
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping
Creating Primary Key Mysql
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
Create Composite Key Mysql
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 create unique key mysql (`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 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
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 creating foreign key sql server about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users foreign key on update 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 drop a foreign key constraint other. Join them; it only takes a minute: Sign up Error creating foreign key up vote 0 down vote favorite I have been searching both SO and Google for this issue, but what i came up with was http://stackoverflow.com/questions/11926324/foreign-key-constrain-fails-with-error-creating-foreign-key-on-table-check-d 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 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 http://stackoverflow.com/questions/14801458/error-creating-foreign-key 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 votes up vote 4 down vote accepted From your table schema, your columns are not identical. warehouses.id is UNSIGNED NOT NULL where warehouse_product_history.from is signed, DEFAULT NULL. share|improve this answer answered Feb 10 '13 at 21:34 Kermit 27.9k74881 You are absolute
here for a quick overview of the site Help Center Detailed answers to http://stackoverflow.com/questions/33717930/error-creating-foreign-key-check-data-types 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 foreign key 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 (check data types) up vote 1 down vote favorite I'm trying to create a relation between two tables. And here is the query creating foreign key for each table and the foreign key creation, CREATE TABLE IF NOT EXISTS `quotes` ( `id` 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 R