Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Boston, MA
    Posts
    4

    Unanswered: Error in dropping a foreign key and adding a new one

    I am using 4.0.14-max-debug and have more than a dozen innodb and haevn't had any problems except now when i am trying to change the structure of a table.

    I successfully created the tuning table with a FK referring to program table. I added an additional programvehicle table with a prgid. The programvehicle table with FK for program table. Now i want to drop the FK for program from tuning and instead add FK for the id of programvehicle. However, i cannot remove the foreign key. I get the following error messages :
    ------------------------------------------------------------------
    mysql> alter table tuning drop foreign key;
    ERROR 1005: Can't create table '.\tuningdb1\#sql-304_5.frm' (errno: 150)

    mysql> alter table tuning drop column prgcode;
    ERROR 1025: Error on rename of '.\tuningdb1\#sql-304_5' to '.\tuningdb1\tuning'
    (errno: 150)

    mysql> alter table tuning drop index progcode;
    ERROR 1091: Can't DROP 'progcode'. Check that column/key exists

    mysql> alter table tuning add index(progcode);
    ERROR 1072: Key column 'progcode' doesn't exist in table

    -----------------------------------------------------------------------------

    The strange thing is that when i do a show columns in can see progcode which is a key :

    mysql> show columns from tuning;
    +---------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+------------------+------+-----+---------+----------------+
    | tid | int(10) unsigned | | PRI | NULL | auto_increment |
    | tname | varchar(15) | YES | | NULL | |
    | ttype | varchar(10) | YES | | NULL | |
    | tlocation | varchar(10) | YES | | NULL | |
    | last_modified | timestamp(12) | YES | | NULL | |
    | tspkprefix | text | YES | | NULL | |
    | tdulos | text | YES | | NULL | |
    | tcomment | text | YES | | NULL | |
    | tsrc | varchar(15) | | | | |
    | huid | int(10) unsigned | YES | MUL | NULL | |
    | prgcode | varchar(5) | YES | MUL | NULL | |
    | tdate | date | YES | | NULL | |
    +---------------+------------------+------+-----+---------+----------------+
    12 rows in set (0.00 sec)

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    Well, I don't user ALTER TABLE (I just DROP and reCREATE
    my tables), but I see three problems.

    The MySQL documentation for 'alter table <table> drop
    foreign key ...' says you should use this syntax:

    ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id

    Which means you left out the name of the foreign key.
    You can see this constraint name by doing a:
    SHOW CREATE TABLE <tablename>;

    The second problem is a simple typo:
    you are referencing the field 'prgcode' with 'progcode'.

    A third problem is you didn't set field 'prgcode' to 'NOT NULL'.
    (You are allowing NULLs in an index field.)

    -lv

Posting Permissions

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