How to drop a column with foreign key in MySQL?

Let’s say I used the following structure to create a table:

CREATE TABLE staff (
    sid INT AUTO_INCREMENT,
    sfname VARCHAR(30) NOT NULL,
    slname VARCHAR(30) NOT NULL,
    uid int UNIQUE,
    bid int NOT NULL,
    PRIMARY KEY (sid),
    FOREIGN KEY (uid) REFERENCES sajilo_user(uid),
    FOREIGN KEY (bid) REFERENCES branch(bid)
);

Now I want to add DELETE CASCADE for the foreign key and I found that I can achieve it by dropping the column(column with foreign key) and adding it again with DELETE CASCADE property on alter table statement.

But when I tried:

ALTER TABLE staff DROP column uid;
I got error:  #1553 - Cannot drop index 'u_user': needed in a foreign key constraint.

So I need to remove foreign key constraint first with:

ALTER TABLE staff DROP FOREIGN KEY foreign_key_constraint_name;

As you saw above table was created without giving the name for constraint. I am having trouble to drop it. So what should be done ? I need the way of dropping the column along with foreign key constraint:)

Answer

Finally, I found the solution, We can use information_schema to retrieve the name of a foreign key and can use the name event constraint name is not set explicitly as shown above.

As of MySQL docs: INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

For more information visit: https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

So we can do something like:

USE information_schema;
SELECT * FROM `INNODB_SYS_FOREIGN_COLS`;

Where INNODB denotes a storage engine, for more information visit: https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

After executing the query we can get the result something like:

ID                                  FOR_COL_NAME  REF_COL_NAME     POS  
----------------------------------  ------------  ------------  --------
acdb/takes_ibfk_1                   sid           sid                  0
acdb/takes_ibfk_2                   cid           cid                  0
sajilo_courier/admin_branch_ibfk_1  aid           aid                  0
sajilo_courier/admin_branch_ibfk_2  bid           bid                  0
sajilo_courier/admin_ibfk_1         uid           uid                  0
sajilo_courier/admin_staff_ibfk_1   aid           aid                  0
sajilo_courier/admin_staff_ibfk_2   sid           sid                  0
sajilo_courier/staff_ibfk_1         uid           uid                  0

As we can see in the ID contains the name of database/foreign key name so finally we can do something like

ALTER TABLE staff DROP FOREIGN KEY staff_ibfk_1 ;

From last row and finally we can drop the column easily 🙂