Bug #80529 Copying a PK-column as reference into another table impossible to remove AI
Submitted: 26 Feb 2016 13:10 Modified: 26 Feb 2016 14:05
Reporter: Peter Benovic Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:6.3.6 OS:Microsoft Windows (Microsoft Windows 10 Pro)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[26 Feb 2016 13:10] Peter Benovic
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.
[26 Feb 2016 13:38] Miguel Solorzano
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

http://bugs.mysql.com/bug.php?id=80528

Thank you for your interest in MySQL.
[26 Feb 2016 14:05] Peter Benovic
Sorry Miguel, I'm obviously a lousy searcher. I searched but did not find the earlier report on this bug.