var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Modify Foreign key
I'm wondering if it's possible to modify a Foreign Key constraint. Let's I've the following Foreign Key constraint defined for a wrongs table:
FOREIGN KEY (member_id, level) REFERENCES scores (member_id, level) ON DELETE CASCADE,
And I would like to change it to:
FOREIGN KEY (member_id, level, subject) REFERENCES scores (member_id, level, subject) ON DELETE CASCADE,
Is it possible?
Thanks in anticipation
This works in Oracle but doesn't seem to work in mysql :
ALTER TABLE wrongs MODIFY CONSTRAINT fk FOREIGN KEY (member_id, level, subject) REFERENCES scores (member_id, level, subject) ON DELETE CASCADE;
where fk is the name of your foreign key constraint.
I think in mysql you have to do this :
ALTER TABLE wrongs DROP FOREIGN KEY fk;
ALTER TABLE wrongs ADD CONSTRAINT fk FOREIGN KEY (member_id, level, subject) REFERENCES scores (member_id, level, subject) ON DELETE CASCADE;
I didn't name my Foreign Key constraint so I typed:
alter table wrongs drop foreign key;
I got the error:
ERROR 1005: Can't create table '.\test\@sql-868_44.frm' (errno: 150)
Okay I found the way to do it:
SHOW CREATE TABLE wrongs\G
That shows the table specs, including the Foreign Key Constraint ID.
Then I type:
ALTER TABLE wrongs DROP FOREIGN KEY 0_155;
Assuming 0_155 is the Foreign Key Constraint ID displaying from the output of SHOW CREATE TABLE wrongs\G
After that, I added the new Foreigh Key Constraint and it worked
Way to go !
But from now on, though, I suggest that you always name your constraints (primary or foreign keys, or other ones), because it is easier to retrieve them afterwards if you want to drop or modify them.
I don't know why I didn't think of adding a constraint name. It would be really useful to have one. Thanks man