Description:
There is a master table with a primary key. I created the detail table and copied the primary key column of the master table into the detail table to act as a foreign key. In the Master table the column had the attributes PK, NN and AI which I removed in the details table by clicking the appropriate checkboxes. However, when synchronising the model with the database the column still has the AI attribute und thus table creation fails.
Even restarting MySQL Workbench has no effect on this behavior.
As I can't find any link to upload the log file I have copied the relevant part here (please note the column named `VerleihperiodeID`):
22:23:25 [INF][ grt_diff]: Restoring oldNames and other sync state info for Mysql@Peter-W520:3306::bringadb (catalog {3230EEB1-10B6-4DD2-BCE7-FD9ABE1D38CA})
22:23:26 [INF][ grt_diff]: Remove triggers from copy of model schema bringadb
22:23:26 [INF][ grt_diff]: Remove routines from copy of model schema bringadb
22:25:17 [INF][ grt]: Applying synchronization scripts to server...
22:25:17 [INF][ grt]: Executing SQL script in server
22:25:17 [ERR][ grt]: Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key
SQL Code:
CREATE TABLE IF NOT EXISTS `bringadb`.`VerleihArtikel` (
`VerleihArtID` INT(11) NOT NULL AUTO_INCREMENT,
`VerleihID` INT(11) NOT NULL,
`ArtID` INT(11) NOT NULL,
`Anzahl` INT(11) NOT NULL,
`Bezeichnung` VARCHAR(64) NOT NULL,
`VerleihRate` DECIMAL(12,2) NOT NULL,
`VerleihperiodeID` INT(11) NOT NULL AUTO_INCREMENT,
`Notiz` VARCHAR(128) NULL DEFAULT NULL,
PRIMARY KEY (`VerleihArtID`),
INDEX `FK_VerleihArtikel_Verleih_idx` (`VerleihID` ASC),
INDEX `FK_Verleihartikel_Artikelstamm_idx` (`ArtID` ASC),
CONSTRAINT `FK_VerleihArtikel_Verleih`
FOREIGN KEY (`VerleihID`)
REFERENCES `bringadb`.`Verleih` (`VerleihID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `FK_Verleihartikel_Artikelstamm`
FOREIGN KEY (`ArtID`)
REFERENCES `bringadb`.`Artikelstamm` (`ArtID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `FK_VerleihArtikel_VerleihPerioden`
FOREIGN KEY (`VerleihPeriodeID`)
REFERENCES `bringadb`.`Verleihperioden` (`VerleihperiodeID`)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
22:25:17 [INF][ grt]: SQL script execution finished: statements: 6 succeeded, 1 failed
How to repeat:
Model -> Add Table (Details table)
Model -> Edit 'Table' in new Tab... (open Master table)
In the new table add columns. Then go to master table, select the primary key column, right-click and select Copy from the context menu. Then go back to the new details table, right-click and click on Paste. The column will be copied with all its attributes. Click on the checkbox under AI so that it is unchecked.
Save the Model (Ctrl-S) and select Database -> Synchronize Model... from the main menu.
In the resulting CREATE TABLE statement the copied column still has the AUTO_INCREMENT attribute and the statement fails with the message that only one column can be AI.