How to change a foreign key constraint in MySQL
Tags: mysql, database, tutorial, sql
Published: 2020-05-18 19:31:46

---
title: How to change a foreign key constraint in MySQL
published: true
description: This is how you change a foreign key constraint in MySQL
tags: mysql,database,tutorial,sql
---

# Alter a foreign key constraint in MySQL

(PS...if you want to skip to just the answer <a href="#tldr" title="Go to the TL;DR">Click this link</a>)

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:

```SQL
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:

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

or like this
```SQL
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:
```SQL
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:
```SQL
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:

```SQL
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!

<span id="tldr"></span>


<h1>TL;DR</h1>

1. Get the name of your foreign key constraint:
	If you don't know the name, run this command:
	```SQL
	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:
	```SQL
	ALTER TABLE your_table_name_here DROP FOREIGN KEY name_of_your_constraint;
	```
3. Create your new foreign key constraint:
	```SQL
	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