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?

回答1:

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.

回答2:

have you tried the alter table command?

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