Foreign Key Mysql How to change a foreign key constraint in MySQL

Alter a foreign key constraint in MySQL

(PS...if you want to skip to just the answer Click this link)

I ran into a problem today.

I found a foreign key in my table that cascaded instead of set the key as null.

As you can imagine, this was not a great thing to learn. I lost quite a few log records. Luckily, this is an application used only by me, and I had backups that I could restore to that lost very little data. But finding how to reverse this foreign key effect proved challenging.

When I went to Google for my answer, I found it challenging. There isn't much information about how to alter foreign keys in MySQL.

There is no command like:

ALTER TABLE my_table CHANGE FOREIGN KEY (key_name) REFERENCES other_table(other_key) ON DELETE SET NULL;

That would be sublime if that existed!! Alas, it doesn't, so we must find a workaround. Here is what the workaround looks like.

First - Get the name of your foreign key constraint.

If you're like me, you create your tables (and constraints) like this:

CREATE TABLE my_table (
    name varchar(255),
    key int(11),
    FOREIGN KEY key REFERENCES other_table(id) ON DELETE SET NULL
);

or like this

ALTER TABLE my_table ADD FOREIGN KEY (key) REFERENCES other_table(id) ON DELETE CASCADE;

Without the full MySQL name of the constraint. So how do we get the name that MySQL needs to drop the constraint?

Enter the SHOW CREATE TABLE command.

The output of this command contains the name of the constraint that you need. Here is what that output would look like for my above CREATE TABLE (or ALTER TABLE) command:

SHOW CREATE TABLE my_table\G

*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `name` varchar(255) DEFAULT NULL,
  `key` int(11) DEFAULT NULL,
  KEY `ke` (`key`),
  CONSTRAINT `my_table_ibfk_1` FOREIGN KEY (`key`)
  REFERENCES `other_table` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

So the name of our foreign key constraint would be "my_table_ibfk_1";

Second - Drop the foreign key constraint.

Now that we have the name to our foreign key constraint, we need to drop it. And this is the part that held me up the most.

I didn't know the consequences to dropping a foreign key constraint. I was afraid because of my own ignorance.

Turns out, there was nothing to be afraid of. If you drop the foreign key constraint, it doesn't have any other effects, and keeps all values intact.

So, here's the code to run to do that:

ALTER TABLE my_table DROP FOREIGN KEY my_table_ibfk_1;

Third - Create your new foreign key constraint

In my case, we need to change the cascade to a set null command. Here is how you would do that:

ALTER TABLE my_table ADD FOREIGN KEY (key) REFERENCES other_table(id) ON DELETE SET NULL;

And that's it!! That's how you change a foreign key constraint in MySQL!

TL;DR

1. Get the name of your foreign key constraint:
   If you don't know the name, run this command:
   

   SHOW CREATE TABLE your_table_name_here\G
   

   And you will get an output that looks something like this:
   

   *************************** 1. row ***************************
          Table: your_table_name_here
   Create Table: CREATE TABLE `your_table_name_here` (
     `id` int DEFAULT NULL,
     `parent_id` int DEFAULT NULL,
     KEY `par_ind` (`parent_id`),
     CONSTRAINT `name_of_your_constraint` FOREIGN KEY (`parent_id`)
     REFERENCES `parent` (`id`) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   

   Which is where the name of your constraint will be (After the keyword "Constraint". There may be several, but you should be able to pick yours out).

2. Drop your foreign key constraint:
   

   ALTER TABLE your_table_name_here DROP FOREIGN KEY name_of_your_constraint;
   

3. Create your new foreign key constraint:
   

   ALTER TABLE your_table_name_here ADD FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL;
   

And that's it! That's how you change your foreign key constraint in MySQL

Comments

[Peter Aylin]

Thank you!