Learning PHP 7 Antonio Lopez

Prev Next

Foreign key behaviors

One tricky thing to manage when updating or deleting rows is when the row that we update is part of a foreign key somewhere else. For example, our borrowed_books table contains the IDs of customers and books, and as you already know, MySQL enforces that these IDs are always valid and exist on these respective tables. What would happen, then, if we changed the ID of the book itself on the book table? Or even worse, what would happen if we removed one of the books from book, and there is a row in borrowed_books that references this ID?

MySQL allows you to set the desired reaction when one of these scenarios takes place. It has to be defined when adding the foreign key; so, in our case, we will need to first remove the existing ones and then add them again. To remove or drop a key, you need to know the name of this key, which we can find using the SHOW CREATE TABLE command, as follows:

mysql> SHOW CREATE TABLE borrowed_books \G
*************************** 1. row ***************************
       Table: borrowed_books
Create Table: CREATE TABLE `borrowed_books` (
  `book_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
  KEY `book_id` (`book_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `borrowed_books_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `borrowed_books_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The two foreign keys that we want to remove are borrowed_books_ibfk_1 and borrowed_books_ibfk_2. Let's remove them using the ALTER TABLE command, as we did before:

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_1;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_2;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Now, we need to add the foreign keys again. The format of the command will be the same as when we added them, but appending the new desired behavior. In our case, if we remove a customer or book from our tables, we want to remove the rows referencing these books and customers from borrowed_books; so, we need to use the CASCADE option. Let's consider what they would look like:

mysql> ALTER TABLE borrowed_books
    -> ADD FOREIGN KEY (book_id) REFERENCES book (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE,
    -> ADD FOREIGN KEY (customer_id) REFERENCES customer (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Note that we can define the CASCADE behavior for both actions: when updating and when deleting rows. There are other options instead of CASCADE—for example SET NULL, which sets the foreign keys columns to NULL and allows the original row to be deleted, or the default one, RESTRICT, which rejects the update/delete commands.

Prev Next