CODEWITHSUNDEEP
mysqlforeign-keys
Rewind
Rewind

Reputation: 2814

How to alter Foreign Keys in phpMyAdmin

I have set up a Foreign Key in my mysql database with:

ALTER TABLE `gameplayers` ADD CONSTRAINT `FK_GAMENUMBER` FOREIGN KEY (`GameNumber`) REFERENCES `games`(`GameNumber`) ON UPDATE CASCADE ON DELETE CASCADE;

However, I am not sure I want the ON UPDATE and ON DELETE anymore.

So I go into my phpAdmin, click on the edit pencil icon in the Index section of the Structure Tab and I get this:

Warning: ("PRIMARY" must be the name of and only of a primary key!)

Do alterations just have to be done manually? Ie the pencil icon will just not work.

ALSO: Do foreign keys have the same speed bonus effect on mysql searches, similar to Indexes?

Upvotes: 3

Views: 23635

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26749

Foreign keys require indexes, so effectively, the foreign key constrain creates and index and it can be used to resolve queries just like normal indexes.

I'm not sure which version of phpMyAdmin you are using, I think foreign key constains are supported in newest versions, but it seems your does not list foreign key indexes, and the primary key is not what you are looking for. You can however modify the keys with plain SQL:

ALTER TABLE `gameplayers` DROP FOREIGN KEY FK_GAMENUMBER,
    ADD CONSTRAINT `FK_GAMENUMBER` FOREIGN KEY (`GameNumber`) REFERENCES `games`(`GameNumber`) ON UPDATE NO ACTION ON DELETE NO ACTION;

Upvotes: 8

Related Questions