Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Aug 19, 2003

Referential Integrity in MySQL - Page 2

By Ian Gilfillan



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

» See All Articles by Columnist Ian Gilfillan



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM