Reputation: 14761
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
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
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
Reputation: 113
have you tried the alter table command?
http://www.w3schools.com/sql/sql_foreignkey.asp
Upvotes: 0