CODEWITHSUNDEEP
mysqlforeign-keys
Mahdi_Nine
Mahdi_Nine

Reputation: 14761

how can i modify foreign key?

I'm wondering if it's possible to modify a Foreign Key?

FOREIGN KEY (member) REFERENCES scores (level) ON DELETE CASCADE,

And I would like to change it to:

FOREIGN KEY (member, subject) REFERENCES scores (level, subject) ON DELETE set null,

Is it possible?

Upvotes: 5

Views: 10146

Answers (3)

Nasser Boukehil
Nasser Boukehil

Reputation: 461

In MySql, you can accomplish that by following Tom Tresansky response, which will give us this syntax:

ALTER TABLE `table_name` DROP FOREIGN KEY `key_name`;
ALTER TABLE `table_name` ADD CONSTRAINT `constraint_name` FOREIGN KEY (`new_key_name`) 
REFERENCES `other_table_name` ('other_table_id') ON UPDATE CASCADE ON DELETE CASCADE;

Upvotes: 2

Tom Tresansky
Tom Tresansky

Reputation: 19877

You cannot modify the key in a single statement, see the ALTER TABLE syntax, in which there is no ALTER CONSTRAINT available.

You must use 2 ALTER TABLE statements to accomplish what you want.

Delete the key in the first one using an ALTER TABLE DROP FOREIGN KEY. Re-create it with the new columns in the second, using an ALTER TABLE ADD CONSTRAINT FOREIGN KEY.

You can encapsulate both within a single transaction to make an atomic modification.

Upvotes: 3

danhumphries89
danhumphries89

Reputation: 113

have you tried the alter table command?

http://www.w3schools.com/sql/sql_foreignkey.asp

Upvotes: 0

Related Questions