MySQL Bugs: #53117: Difficulty in setting a foreign key

Bug #53117	Difficulty in setting a foreign key
Submitted:	23 Apr 2010 15:15	Modified:	29 May 2010 13:13
Reporter:	Joshua Levin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.19 OSS Beta Rev. 5680	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
For the purpose of this discussion, we need to consider only two tables of the database, a PUBLICATIONS table showing a list of publications, and an ISSUES table showing all future issue dates for a couple of years, that keys into that first table.

The PubID column of the ISSUES table is supposed to be a Foreign Key into the PubID column of the PUBLICATIONS table.  However, over the "Foreign Keys" tab, the only choice given for the Reference Column for this is another column, IssuesPerYear, in the PUBLICATIONS table.  See the attached screen shot (if available).

I can easily edit this when I get the table structure in textual form, but it is still a nuisance.

How to repeat:
Other tables in the database do not seem to have this problem, but they have more than one Foreign Key per table.

Suggested fix:
Make all pertinent columns available in the pull-down list.

"Foreign Keys" tab, showing single-entry pull-down

Attachment: WB-ScreenShot-2.jpg (image/jpeg, text), 32.23 KiB.

Can you upload the .mwb file for this model or CREATE TABLE statements for the tables?

Sorry, the .mwb file has some confidential information.  No can do.

The SQL queries to generate the PUBLICATIONS table is as follows (some comments have been removed for security reasons):

CREATE  TABLE IF NOT EXISTS `mydb`.`PUBLICATIONS` (
  `PubID` INT NOT NULL ,
  `PubName` VARCHAR(80) NULL ,
  `PubAbbrev` VARCHAR(8) NULL ,
  `PubAbbrevAlt` VARCHAR(8) NULL ,
  `IssuesPerYear` SMALLINT(6)  NULL ,
  PRIMARY KEY (`PubID`) ,
  INDEX `PubID` (`PubID` ASC) )
ENGINE = InnoDB
COMMENT = 'List of Publications'

Thank you for the feedback.

What is the type of PubID field of ISSUES table?

PubID is INT2

Well, because of the question, I noticed that PubID was INT in the PUBLICATIONS table, and INT2 in the ISSUES table.  I changed it to be INT in both places, and -- guess what -- the error is still there!  It still says `IssuesPerYear`

Thank you for the feedback.

Please change it to INT(11) and inform us if this helps.

I tried changing PubID to INT(1) in both places, and it did not help.  I also tried INT(11) in both places, and it didn't help either.

Did you already try to drop the foreign key and re-create it?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".