MySQL Forums
Forum List  »  InnoDB

Problem creating FK constraint
Posted by: Roy Wilkinson
Date: July 07, 2013 04:08PM

I'm getting an exception when creating a table with a foreign key, while a previous table creation with the same FK executes without error. The error I'm getting is:

"Can't write; duplicate key in table 'instrumentations'"

I'm using the exported 'create script' feature of MySQL Workbench. My tables, as defined in the MySQL Workbench are:

=======

"Notes":
NoteID INT (Primary Key, Not Null, Unsigned, AutoIncrement)
NoteText TEXT

"Composers":
ComposerID INT (Primary Key, Not Null, Unsigned, AutoIncrement)
ComposerName VARCHARS(50) (Not Null, Unique)
NoteID INT (Unsigned)
Foreign Key Name: 'NoteID', Ref Table: 'Notes', Column: 'NoteID', Ref Column: 'NoteID'

"Instrumentations":
Instrumentation VARCHAR(40) (Primary Key, Not Null)
ListOrder INT (Not Null, Unsigned)
NoteID INT (Unsigned)
Foreign Key Name: 'NoteID', Ref Table: 'Notes', Column: 'NoteID', Ref Column: 'NoteID'

=======

The resulting script is (essentially):

=======

USE `dbname`;

CREATE TABLE `Notes` (
`NoteID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`NoteText` TEXT NULL ,
PRIMARY KEY (`NoteID`) )
ENGINE = InnoDB;

CREATE TABLE `Composers` (
`ComposerID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`ComposerName` VARCHAR(50) NOT NULL,
`NoteID` INT UNSIGNED NULL,
PRIMARY KEY (`ComposerID`),
UNIQUE INDEX `ComposerName_UNIQUE` (`ComposerName` ASC),
INDEX `NoteID_idx` (`NoteID` ASC),
CONSTRAINT `NoteID`
FOREIGN KEY (`NoteID`)
REFERENCES `Notes` (`NoteID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE `Instrumentations` (
`Instrumentation` VARCHAR(40) NOT NULL,
`ListOrder` INT UNSIGNED NOT NULL,
`NoteID` INT UNSIGNED NULL,
INDEX `NoteID_idx` (`NoteID` ASC),
PRIMARY KEY (`Instrumentation`),
CONSTRAINT `NoteID`
FOREIGN KEY (`NoteID`)
REFERENCES `ppmstrqt`.`Notes` (`NoteID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

=======

Transferring these commands to my C# code (using the MySQL Connector NET 6.6.5), I get the following exception when trying to create the 'Instrumentations' table:

"Can't write; duplicate key in table 'instrumentations'"

However, if, instead, I use:

=======

CREATE TABLE `Instrumentations` (
`Instrumentation` VARCHAR(40) NOT NULL,
`ListOrder` INT UNSIGNED NOT NULL,
`NoteID` INT UNSIGNED NULL REFERENCES `Notes` (`NoteID`),
INDEX `NoteID_idx` (`NoteID` ASC),
PRIMARY KEY (`Instrumentation`) )
ENGINE = InnoDB;

=======

The table creation occurs without incident.

The previous table creation, 'Composers', has the exact same FK definition.

If I go back to the MySQL Workbench and redefine the 'NoteID' fields in 'Composers' and 'Instrumentations' to "ComposerNoteID" and "InstrNoteID" respectively, and use the resulting "CREATE" script info, I get the same exception in the same place.

What am I missing?

Thanks for any help you can provide.

- Roy

I'm using:
MySQL 5.6 CE
MySQL Workbench 5.2 CE
MySQL Connector Net 6.6.5

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem creating FK constraint
1678
July 07, 2013 04:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.