Geek Answers Handbook

PRIMARY and INDEX keys for one FOREIGN column in MySQL

I used MySQL Workbench to prepare the database layout and exported it to my database using phpMyAdmin. When looking at one table, I received the following warning:

PRIMARY and INDEX keys should not be set for gid column

gid is an external index that is the primary key of another table and which is also part of the primary key of the current table. So I have this as part of the primary key, and Workbench created an index to enter the foreign key. So, why does this warning appear, should I ignore it or review my database layout?

This is a very simplified example of the structure used, which generates a warning:

 CREATE TABLE IF NOT EXISTS `test_groups` ( `gid` INT NOT NULL , `gname` VARCHAR(45) NULL , PRIMARY KEY (`gid`) ); CREATE TABLE IF NOT EXISTS `test_users` ( `gid` INT NOT NULL , `uid` INT NOT NULL , `name` VARCHAR(45) NULL , PRIMARY KEY (`gid`, `uid`) , INDEX `gid` (`gid` ASC) , CONSTRAINT `gid` FOREIGN KEY (`gid` ) REFERENCES `test_groups` (`gid` ) ON DELETE CASCADE ON UPDATE CASCADE);

edit I tried removing the extra index for gid in phpMyAdmin and it seems to work. Cascading action still occurs when something changes in the group table, so I think that the external relation is not damaged even without an index.

But why does MySQL Workbench force me to maintain this index? I cannot manually delete it there while there is a foreign key.

+4
source share
2 answers

I have solved this problem now. It looks like the default database storage engine was installed on MyISAM on my server, so since I did not explicitly specify it, all relations with the foreign key were simply dropped (not to mention this). After converting it to InnoDB, I no longer receive a warning, so everything seems to work as it should.

However, in this special case, I will stick with MyISAM and leave the external external relations outside because I want to automatically increase the second attribute in this multi-key (and this is not supported by InnoDB) and this is a little more useful for this application than having foreign keys (especially if there is data in which updating and deletion will be very rare).

Also regarding MySQL Workbench, this behavior still seems a bit buggy, and it has already been reported .

0
source

There is nothing wrong. Even if it was the entire primary key of the current table, it is still potentially correct. Indeed, if you are not one of those "programmers" who only ever use auto-increment columns for primary keys, you will see that it says a lot.

+2
source

More articles:


All Articles