Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Modify Foreign key

    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

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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)

  4. #4
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    I don't know why I didn't think of adding a constraint name. It would be really useful to have one. Thanks man

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •