Dropping a column with a foreign key

Question

On my mysql DB with inno_db engine,

I have a table with a foreign key. I want to drop the column (along with the foreign key and the associated index of course - i don't need the whole column!)

Now, simply dropping it yields an error: General error: 1025 Error on rename of '.\road_dmy#sql-19d8_2be' to '.\road_dmy\contact' (errno: 150)

It sounds like this is a known issue. http://bugs.mysql.com/bug.php?id=15317

But anyway, what should i do to drop this column? I'm very sure it's possible nobody would use this DB otherwise

(and b.t.w. how can I know the true details of the mysterious error message above?)

Answer 1

You must drop the key first. I don't know the names of your tables but I'll give you the general strategy by example. Suppose you have the following 2 InnoDB tables:

CREATE TABLE `A` (
   `id` int(10) unsigned NOT NULL auto_increment,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `B` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `a_id` int(10) unsigned NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `a_id` (`a_id`),
    CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB;

You can drop the a_id column in table B using the following command:

alter table B drop foreign key b_ibfk_1, drop column a_id;

Answer 2

To view all the foreign keys related to the table use the following command:

mysql> show create table <your_table_name>;

This command will output the create table query which contains the foreign key constraints, for example:

       Table: <your_table_name>
Create Table: CREATE TABLE `<your_table_name>` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `unwanted_col` int DEFAULT NULL, 
  KEY `unwanted_col` (`unwanted_col`),
  CONSTRAINT `<your_table_name>_ibfk_1` FOREIGN KEY (`unwanted_col`)
  REFERENCES `parent_table` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now use the below command to drop the foreign key relation:

mysql> ALTER TABLE <your_table_name> DROP FOREIGN KEY `<your_table_name>_ibfk_1`;

Now that your foreign key relation is gone you can safely delete the column:

ALTER TABLE <your_table_name> DROP COLUMN unwanted_col;

Please refer: Dropping Foreign Key Constraints