error creating foreign key on item_id check data types
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 error creating foreign key on name (check data types) about Stack Overflow the company Business Learn more about hiring developers or posting
Error Creating Foreign Key On Table (check Data Types)ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack #1452 - cannot add or update a child row: a foreign key constraint fails Overflow is 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 t
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 content. You can find out more information about the move and how to open a new account (if necessary) here. If you get stuck you can get support by emailing firstname.lastname@example.org 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. http://stackoverflow.com/questions/11926324/foreign-key-constrain-fails-with-error-creating-foreign-key-on-table-check-d To start viewing messages, select the forum 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 http://www.sitepoint.com/forums/showthread.php?622949-Error-creating-foreign-key-on-gender-(check-data-types) Join Date Jan 2008 Posts 16 Mentioned 0 Post(s) 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)
Foreign Key Constraints 2. Enabling Foreign Key Support 3. Required and Suggested Database Indexes 4. Advanced Foreign Key Constraint Features 4.1. Composite Foreign Key Constraints 4.2. Deferred Foreign https://www.sqlite.org/foreignkeys.html Key Constraints 4.3. ON DELETE and ON UPDATE Actions 5. CREATE, http://www.w3resource.com/sqlite/sqlite-constraint.php ALTER and DROP TABLE commands 6. Limits and Unsupported Features Overview This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19. The first section introduces the concept of an SQL foreign key by example and defines the terminology used for the remainder foreign key of the document. Section 2 describes the steps an application must take in order to enable foreign key constraints in SQLite (it is disabled by default). The next section, section 3, describes the indexes that the user must create in order to use foreign key constraints, and those that should be created in order for foreign key constraints error creating foreign to function efficiently. Section 4 describes the advanced foreign key related features supported by SQLite and section 5 describes the way the ALTER and DROP TABLE commands are enhanced to support foreign key constraints. Finally, section 6 enumerates the missing features and limits of the current implementation. This document does not contain a full description of the syntax used to create foreign key constraints in SQLite. This may be found as part of the documentation for the CREATE TABLE statement. 1. Introduction to Foreign Key Constraints SQL foreign key constraints are used to enforce "exists" relationships between tables. For example, consider a database schema created using the following SQL commands: CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER -- Must map to an artist.artistid! ); The applications using this database are entitled to assume that for each row in the track table there exists a corresponding row in the artist table. After all, the comment in the declaration says so.