MySQL Bugs: #7485: When foreign key was added, the automatically created index is not shown

Bug #7485	When foreign key was added, the automatically created index is not shown
Submitted:	22 Dec 2004 15:26	Modified:	6 Feb 2005 5:35
Reporter:	Mark Junker	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.3	OS:	Windows (WinXP Pro SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

when I add a foreign key for a column that is not indexed yet, the automatically created index doesn't show up in the list of indices.

Regards,
Mark

How to repeat:
1. Create a table with the following statement:

CREATE TABLE `test`.`test7` (
  `test7_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `test8` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`test7_id`)
)
TYPE = InnoDB;

2. Create another table with the following statement:

CREATE TABLE `test`.`test8` (
  `test8_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `test7_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`test8_id`)
)
TYPE = InnoDB;

3. Open the "Table Editor" window for table "test8"
4. Add a foreign key for column "test7_id" which points to "test7"."test7_id"
5. Click "Apply Changes" button

The command

ALTER TABLE `test`.`test8` ADD CONSTRAINT `FK_test8_1` FOREIGN KEY `FK_test8_1` (`test7_id`)
    REFERENCES `test7` (`test7_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

will be executed

6. Select the "Indices" tab

As you can see now the automatically created index for column "test7_id" will not show up ... only the "PRIMARY" index.

When you close the table editor and re-open it for table "test8", a new index called "FK_test8_1" is shown, but it should be shown directly after you added the foreign key (step 7).

We relay on the server to add the index. Therefore we do not add the index explicitly in the table editor.

In my opinion the index that is created automaticly is only a workaround to make FKs work. I just want to create a FK and the fact that the server adds a index to do so is only secondary. But I don't know how other databases handle this.

Could you list the reason why you would like to see the index instantly? I wonder what would happen if the user removes the index manually after he created the FK... as the server will re-add the index when you press Apply Changes. I guess we would get a bug report for that.

Thanks!

Hi,

I simply need the because the QueryBrowser struggles if I continue working with the same "Table Editor" window after adding the foreign keys. Maybe it should reload the table definition after applying a change?

Currently, I'm too lazy to test it but I think it struggles after adding a foreign key, applying the changes, deleting it and renaming the column of the (now deleted) foreign key. When you try to apply it now, it should produce an invalid SQL statement because the QB isn't aware of the automatically created index.

Mark

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".