CODEWITHSUNDEEP
mysqlforeign-keysprimary-key
poke
poke

Reputation: 388403

PRIMARY and INDEX keys for one FOREIGN column in MySQL

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

PRIMARY and INDEX keys should not both be set for column gid

gid is a foreign index which is the primary key of a different table, and which is also part of the primary key of the current table. So I have it as part of the primary key, and Workbench created an index for the foreign key entry. So why is that warning appearing, should I ignore it, or should I rethink my database layout?

This is a very simplified example of the used structure, which produces the 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 deleting the additional index for gid in phpMyAdmin and it seems to work. The cascade action still happens when changing something in the groups table, so I guess the foreign-relation is intact even without the index.

But why does MySQL Workbench force me to keep that index? I cannot manually remove it there as long as the foreign key is there.

Upvotes: 4

Views: 1628

Answers (2)

poke
poke

Reputation: 388403

I solved that problem now. It seems that the default database storage engine was set to MyISAM on my server, so because I didn't specify it explicitely, all foreign key relations just discarded (without saying so though). After converting it to InnoDB I no longer get the warning, so it seems that everything is working as it should.

However in this special case, I'll stick to MyISAM, and leave the foreign key relations outside for now, because I want to auto increment the second attribute in that multi-key (and that is not supported by InnoDB), and that's a bit more useful for this application than having foreign keys (especially when having data where updating and deleting will be done very rarely).

Also in regards to MySQL Workbench, that behaviour still seems to be a bit buggy, and it was already reported.

Upvotes: 0

symcbean
symcbean

Reputation: 48387

Nothing wrong with that. Even if it were the entire primary key of the current table its still potentially correct. Indeed, unless you're one of those "programmers" who only ever uses autoincrement columns for primary keys, you're going to see this reported a lot.

Upvotes: 2

Related Questions