Error 1822
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings
Failed To Add The Foreign Key Constraint To System Tables
and policies of this site About Us Learn more about Stack Overflow error missing index on columns phpmyadmin the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags incorrect options in foreign key constraint 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
Mysql Error 1215
takes a minute: Sign up SQL Error 1822 : Failed to add the foreign key constaint. Missing index for constraint BUT the index EXISTS up vote 0 down vote favorite I'm trying to add a foreign key with 2 columns. Here is the DDL of table referencing the foreign key : CREATE TABLE IF NOT EXISTS `sf_file_category` ( `id_file_category` INT
Add Foreign Key Mysql
NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NOT NULL, `file_type` ENUM('document', 'image', 'video', 'archive') CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NULL, `id_file_category_parent` INT UNSIGNED NULL, PRIMARY KEY (`id_file_category`), INDEX `fk_sf_file_category_sf_file_category1_idx` (`id_file_category_parent` ASC), INDEX `fk_sf_file_category_sf_file_idx` (`id_file_category` ASC, `file_type` ASC) ) ENGINE = InnoDB; DDL of table who owns the foreign key : CREATE TABLE IF NOT EXISTS `sf_file` ( `id_file` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fullpath` VARCHAR(100) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NOT NULL, `basename` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NOT NULL, `accesskey` CHAR(8) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NOT NULL, `file_type` ENUM('document', 'image', 'video', 'archive') CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NULL, `name` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NULL, `description` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' NULL, `id_aircraft_image` SMALLINT UNSIGNED NULL, `id_aircraft` SMALLINT UNSIGNED NULL, `id_file_category` INT UNSIGNED NULL, PRIMARY KEY (`id_file`), INDEX `fk_sf_file_sf_file_category1_idx` (`id_file_category` ASC, `file_type` ASC), INDEX `fk_sf_file_sf_aircraft1_idx` (`id_aircraft` ASC), INDEX `fk_sf_file_sf_aircraft2_idx` (`id_aircraft_image` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1 COLLATE = latin1_general_ci; Trying to execute following foreign key syntax : ALTER TABLE `sf_file` ADD CONSTRAINT `fk_sf_file_sf_file_category1` FOREIGN KEY (`id_file_category` , `file_type
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 MySQL fails to add a foreign key constraint up vote 1 down vote favorite 2 http://stackoverflow.com/questions/35368643/sql-error-1822-failed-to-add-the-foreign-key-constaint-missing-index-for-cons I added a table 'GameRatings' in the Django model and migrated using South. The table has a one-one relationship with a table called 'Game'. The migration fails with the following error message: 2014-01-07 10:51:15,026] (0.505) CREATE TABLE app_gameratingsmodel (game_id varchar(32) NOT NULL PRIMARY KEY, averageRating double precision NOT NULL, numRatings integer NOT NULL); args=[] [2014-01-07 10:51:15,650] (0.294) ALTER TABLE app_gameratingsmodel ADD CONSTRAINT game_id_refs_gameID_fe5d3728 FOREIGN KEY (game_id) REFERENCES app_gamemodel (gameID);; args=[] FATAL http://stackoverflow.com/questions/20970119/mysql-fails-to-add-a-foreign-key-constraint ERROR - The following SQL query failed: ALTER TABLE app_gameratingsmodel ADD CONSTRAINT game_id_refs_gameID_fe5d3728 FOREIGN KEY (game_id) REFERENCES app_gamemodel (gameID); The error was: (1822, "Failed to add the foreign key constaint. Missing index for constraint 'game_id_refs_gameID_fe5d3728' in the referenced table 'app_gamemodel'") I don't understand what index is MySQL talking about. Can anyone help? Thanks. mysql django django-models django-south database-migration share|improve this question asked Jan 7 '14 at 11:11 mynk 410615 What version of python are you using? –Pete Tinkler Jan 7 '14 at 11:13 Python version: 2.7.2 –mynk Jan 7 '14 at 11:16 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote This occures often when you try reference two columns with different data type (e.g. VARCHAR to INT or even INT to UINT!). I always forget to check the unsigned marker in Workbench :) share|improve this answer answered Jul 31 '14 at 16:05 Brian 512 I had the same problem, your answer saved me. Thank you =) –Sara Jan 11 at 14:10 add a comment| up vote 1 down vote You probably have no UNIQUE constraint on app_gamemodel.gameID. A UNIQUE (or PRIMARY KEY, which is pretty much the same) is needed for a database to be able to e
Reporter: Ed Segall Email Updates: Status: Closed Impact on me: None Category:MySQL Workbench Severity:S3 (Non-critical) Version:5.2.47 CE Revision 10398 OS:Microsoft Windows (Windows 7 Ultimate 64-bit) Assigned to: Alfredo Kengi Kojima View Add Comment Files Developer Edit Submission View Progress Log https://bugs.mysql.com/bug.php?id=69508 Contributions [18 Jun 2013 23:26] Ed Segall Description: When working with tables that include foreign keys in Workbench, it is fairly easy to get into a situation in which the Synchronize Model... wizard on the Database menu results in Error 1822 failures. This happens when the SQL statements generated by the wizard are output in an order that violates the requirement that a foreign key may be added only if a compatible index exists for foreign key the referenced column(s). How to repeat: Create a new model in Workbench, for a new schema called mydb. Ensure a mysql instance is running and that it doesn't have a schema with this name. Create a new table named table1. Create a column named idtable1 (the default name). Use the default type, INT. Create a column named table2col (note: the default name is table1col, but in a later step we will configure this column to be foreign key constraint a foreign key that references table2). Change its type to INT. Create a second table in Workbench, called table2. Create two columns named idtable2 and table2col (the default names), both INT as above. Save the model to a new .mwb file. Synchronize with mysql (menu Database:Synchronize Model...) - Set parameters as needed and click Next. - Assuming execution completed successfully, click Next. - Select the schema (mydb), and click Next. This will create the new schema in the database and create the two tables in their current forms. - Assuming retrieval completed successfully, click Next. - Leave the updates unchanged, and click Next. - Review the SQL (save it to a file if desired), and click Execute. (See below for the SQL that was generated). - Assuming success, click Close. Modify table2 by adding an index, called table2col_idx. Leave its type the default (INDEX), and check the box for column table2col. Modify table1 by adding a foreign key named fk_table1_table2 that references table `mydb`.`table2`. Check the box for column table2col, and for the referenced column, select table2col. Save the model. Synchronize the model again. This time, examine the SQL before clicking Execute. (Save to a file if desired.) Observe that the ALTER TABLE statement for table1 precedes the ALTER TABLE statement for table2. Observe also that the ALTER TABLE statement for table1 adds a