Referential Integrity in MySQL - Page 2

Dropping a foreign key

You cannot just drop a foreign key constraint, as you would an ordinary index. See what happens when you try.

mysql> ALTER TABLE customer_sales DROP FOREIGN KEY;
ERROR 1005: Can't create table './test/#sql-86_6b87c.frm' (errno: 150)

To drop the index, you will need to specify the internally generated foreign key id, which you can see by running

mysql> SHOW CREATE TABLE customer_sales;
| Table          | Create Table                                         
| customer_sales | CREATE TABLE 'customer_sales' (
  'transaction_id' int(11) NOT NULL default '0',
  'amount' int(11) default NULL,
  'customer_id' int(11) NOT NULL default '0',
  PRIMARY KEY  ('transaction_id'),
  KEY 'customer_id' ('customer_id'),
  CONSTRAINT '0_22' FOREIGN KEY ('customer_id') REFERENCES 'customer' 
  ('customer_id')
) TYPE=InnoDB |

1 row in set (0.00 sec)

In my case the constraint was 0_22 - it will probably be different for you, so use the constraint specific to you.

mysql> ALTER TABLE customer_sales DROP FOREIGN KEY 0_22;
Query OK, 2 rows affected (0.00 sec)

This works with MySQL 4.0.13 and later.

Deleting foreign keys

You can delete a record from customer and at the same time delete the record from the customer_sales table, using only one delete statement. This is called a cascading delete, where all associated records are deleted, 'cascading' through the tables according to the foreign key relationships. An alternative is not to delete the related record, but to set the foreign key value to NULL (assuming the field can be NULL - it is not in our example as we have defined customer_id as NOT NULL). The standard options when deleting a foreign key are listed below.

• ON DELETE CASCADE
• ON DELETE SET NULL
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET DEFAULT

ON DELETE RESTRICT is the default, and disallows a delete if an associated record still exists, as we saw above. ON DELETE NO ACTION does the same thing. ON DELETE SET DEFAULT does not currently work in MySQL - it is supposed to set the deleted foreign key value to whatever was defined as the default value. Here is an example of a cascading delete:

mysql> ALTER TABLE customer_sales ADD FOREIGN KEY(customer_id) 
REFERENCES customer (customer_id) ON DELETE CASCADE;
Query OK, 3 rows affected (0.00 sec)

Let's look at what we have before we attempt to delete:

mysql> SELECT * FROM customer;
+-------------+----------+
| customer_id | name     |
+-------------+----------+
|           1 | Nhlanhla |
|           2 | Anton    |
|           3 | Malvin   |
+-------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customer_sales;
+----------------+--------+-------------+
| transaction_id | amount | customer_id |
+----------------+--------+-------------+
|              1 |     23 |           1 |
|              2 |     39 |           3 |
|              3 |     81 |           2 |
+----------------+--------+-------------+
3 rows in set (0.00 sec)

Now we delete Malvin from the database:

mysql> DELETE FROM customer WHERE customer_id=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM customer_sales;
+----------------+--------+-------------+
| transaction_id | amount | customer_id |
+----------------+--------+-------------+
|              1 |     23 |           1 |
|              3 |     81 |           2 |
+----------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customer;
+-------------+----------+
| customer_id | name     |
+-------------+----------+
|           1 | Nhlanhla |
|           2 | Anton    |
+-------------+----------+
2 rows in set (0.00 sec)

He has also been removed from the customer_sales table. Use cascading deletes with care!

From MySQL 4.0.8, similar options can be applied to ON UPDATE, i.e:

• ON UPDATE CASCADE
• ON UPDATE SET NULL
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET DEFAULT

Let's look at a quick example: First, drop the index again (using the constraint specific to your table)

mysql> ALTER TABLE customer_sales DROP FOREIGN KEY 0_42;
Query OK, 2 rows affected (0.00 sec)

mysql> ALTER TABLE customer_sales ADD FOREIGN KEY(customer_id) 
REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 2 rows affected (0.00 sec)

mysql>UPDATE customer SET customer_id=customer_id+10;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM customer;
+-------------+----------+
| customer_id | name     |
+-------------+----------+
|          11 | Nhlanhla |
|          12 | Anton    |
+-------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customer_sales;
+----------------+--------+-------------+
| transaction_id | amount | customer_id |
+----------------+--------+-------------+
|              1 |     23 |          11 |
|              3 |     81 |          12 |
+----------------+--------+-------------+
3 rows in set (0.00 sec)

The updates have cascaded through to the customer_sales table as well.

Additional resources

• Normalizing tables (normalized tables set up a solid structure for implementing foreign keys).
• Introduction to relational databases: foreign keys

» See All Articles by Columnist Ian Gilfillan