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 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 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 foreign key constraint that references `mydb`.`table2` (`table2col` ), and that the ALTER TABLE statement for table2 adds an index `table2col_idx` on column `table2col`.
Clicking Execute will report an error, as follows:
Error 1822: Failed to add the foreign key constaint. Missing index for constraint 'fk_table1_table2' in the referenced table 'table2'
This apparently happens because a foreign key cannot be added until a compatible index exists in the referenced table. In this case the ALTER TABLE statement for table1 depends on the index table2col_idx that is not added to table2 until the next ALTER TABLE statement.
--------------------------------------------------------------------------
As a workaround, save the SQL to a file and manually reorder the two ALTER TABLE statements before executing them via a console.
--------------------------------------------------------------------------
-------------------------------------
SQL for initial Synchronize operation
-------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb`;
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
`idtable1` INT(11) NOT NULL ,
`table2col` INT(11) NULL DEFAULT NULL ,
PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE TABLE IF NOT EXISTS `mydb`.`table2` (
`idtable2` INT(11) NOT NULL ,
`table2col` INT(11) NULL DEFAULT NULL ,
PRIMARY KEY (`idtable2`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-------------------------------------
SQL for second Synchronize operation
-------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT `fk_table1_table2`
FOREIGN KEY (`table2col` )
REFERENCES `mydb`.`table2` (`table2col` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
, ADD INDEX `fk_table1_table2_idx` (`table2col` ASC) ;
ALTER TABLE `mydb`.`table2`
ADD INDEX `table2col_idx` (`table2col` ASC) ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Suggested fix:
When ordering statements during generation, respect foreign key index dependencies in addition to any other ordering that is being done.
