Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Drop Foreign Key

    I've tried to follow all the instructions and posts and came up with this statement but it doesn't work:
    My table is 'horse'
    My foreign key is 'trainer_id'
    Statement:

    alter table horse
    drop foreign key trainer_id;

    I notice that someone else got the same type of error as I did:
    Error on rename of '.\horsedb\horse' to '.\horsedb\#sq12-838-2' (errno: 152)

    I see that the example on the forum indicates I should use internally_generated_foreign_key_id but I don't understand what is being asked for here, if it is not just the name of the foreign key.

  2. #2
    Join Date
    Mar 2011
    Posts
    4

    Drop foreign key

    USE SHOW TABLE command from the mysql command prompt.
    It will show you the key_id. Drop that. To learn in detail about the MySQL ALTER TABLE, this may be useful for you MySQL altering table - MySQL Tutorial | w3eresource

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Here is an example of how it works. There is no easy command to quickly identify the constraints on a table. However, using the SHOW CREATE TABLE command we get the CREATE TABLE statement to recreate the table. This includes the names of the foreign key constraints. Using this information you can then drop the necessary constraints:

    Code:
    mysql> CREATE TABLE parent (id INT NOT NULL,
        ->                      PRIMARY KEY (id)
        -> ) ENGINE=INNODB;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> CREATE TABLE child (id INT, parent_id INT,
        ->                     INDEX par_ind (parent_id),
        ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
        ->                       ON DELETE CASCADE
        -> ) ENGINE=INNODB;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> show create table child;
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                     |
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | child | CREATE TABLE `child` (
      `id` int(11) default NULL,
      `parent_id` int(11) default NULL,
      KEY `par_ind` (`parent_id`),
      CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> alter table child drop foreign key child_ibfk_1;
    Query OK, 0 rows affected (0.11 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Drop Foreign Key

    Thanks for this Ronan. I understand it but I have an error in my create table coding.

    This is what successfully creates my table; it does not, obviously, designate the foreign key, although it's obviously there:
    create table horse
    (
    horse_id int not null auto_increment primary key,
    horse_name varchar(20) not null,
    horse_colour varchar(20),
    horse_sire int not null,
    horse_dam int not null,
    horse_born year(4) not null,
    trainer_id int references trainer(trainer_id)
    ) type=InnoDB;


    If I prepend FOREIGN KEY to my trainer_id, which would then be
    FOREIGN KEY (trainer_id) int references trainer(trainer_id)
    I get an error. What am I doing wrong?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CREATE TABLE horse
    (
    ...
    , trainer_id INTEGER 
    , FOREIGN KEY trainer_fk ( trainer_id ) 
         REFERENCES trainer ( trainer_id )
    )
    mysql doesn't support the REFERENCES attribute on a column declaration, you simply need to declare it separately with the FOREIGN KEY declaration
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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