Hi,

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

Hello,

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;

Regards,

RBARAER

Thanks, RBARAER!

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.

RBARAER

I don't know why I didn't think of adding a constraint name. It would be really useful to have one. Thanks man