how can i modify foreign key?

I'm wondering if it's possible to modify a Fore开发者_开发知识库ign 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?

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.

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;

have you tried the alter table command?

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