Error Creating Foreign Key On Product_id 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 error creating foreign key on name (check data types) Overflow the company Business Learn more about hiring developers or posting ads with us
Error Creating Foreign Key On Table (check Data Types)
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community #1452 - cannot add or update a child row: a foreign key constraint fails 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 signed or unsigned. You may need
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 on
help keep this spread-out data consistent. The essential syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this: [CONSTRAINT https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/create-table-foreign-keys.html [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION index_name http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/innodb-foreign-key-constraints.html represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules: If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used. If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated error creating foreign using the name of the referencing foreign key column. Foreign keys definitions are subject to the following conditions: Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables. Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same. When foreign_key_checks is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is described in SectionÂ
Documentation Library Table of Contents MySQL 5.5 Manual MySQL 5.4 Manual MySQL 5.1 Manual MySQL 5.0 Manual MySQL 3.23/4.0/4.1 Manual Search manual: MySQL 3.23, 4.0, 4.1 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.5 Creating and Using InnoDB Tables :: 13.2.5.4 FOREIGN KEY Constraints « 13.2.5.3 AUTO_INCREMENT Handling in InnoDB 13.2.5.5 InnoDB and MySQL Replication » Section Navigation [Toggle] 13.2.5 Creating and Using InnoDB Tables13.2.5.1 How to Use Transactions in InnoDB with Different APIs 13.2.5.2 Converting Tables from Other Storage Engines to InnoDB 13.2.5.3 AUTO_INCREMENT Handling in InnoDB 13.2.5.4 FOREIGN KEY Constraints 13.2.5.5 InnoDB and MySQL Replication 13.2.5.4. FOREIGN KEY Constraints Starting from MySQL 3.23.44, InnoDB features foreign key constraints. InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this: [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name. Foreign keys definitions are subject to the following conditions: Both tables must be InnoDB tables and they must not be TEMPORARY tables. Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same. InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contra