Foreign Keys Made Simple with MySQL by examples

In plain English : A FOREIGN KEY in one table points to a PRIMARY KEY in another table. A foreign key places constraints on data in the related tables to ensure and guarantee data referential integrity as well as consistency.

Foreign Key Syntax:

create table abc(
abc_id int not null .....
...
hello_id int not null,
FOREIGN KEY (hello_id) REFERENCES myhello(id) ON DELETE RESTRICT ON UPDATE CASCADE)
)ENGINE=InnoDB

Here, the hello_id attribute within the table abc references the attribute id within the table myhello.

We will explain below the syntax in more details with examples.

Let’s go through a simple example for a phone book database containing only two tables : friends, phones.

[info]To go this examples for using Foreign keys with MySQL, you may need to install a WAMP or Lamp as shown in this tutorial.[/info]

1 Create  a database called : myfriends using phpMyAdmin as shown below.

2 Click on the database myfriends on the left side, then click on SQL. Type in the following SQL code:

CREATE TABLE IF NOT EXISTS friends (
  friends_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(200) DEFAULT NULL,
  PRIMARY KEY (friends_id)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS phones (
  phones_id int(11) NOT NULL AUTO_INCREMENT,
  phone varchar(200) DEFAULT NULL,
  friends_id int(11) NOT NULL,
  PRIMARY KEY (phones_id),
  FOREIGN KEY (friends_id) REFERENCES friends(friends_id) 
      ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

3 Click Go to execure the above SQL queries.

4 Click Clear to add new SQL queries. Type in the following SQL queries to insert some data.

INSERT INTO friends (friends_id, name) VALUES
(1, 'imed'),
(2, 'asma');

INSERT INTO phones (phone, friends_id) VALUES
('999', 1),
('121', 1),
('911', 2);

5 Now Try this SQL Query from your phpMyAdmin to delete a friend with id=2:
[success]Note that this contact with id=2, has data referencing him from the phones table[/success]

  delete from friends where friends_id=2

You will get the error shown below:

[info]This is because we used the condition: ON DELETE RESTRICT.  data referencing such contact from the phones tables restricted the delete operation[/info]

6 Now, let’s delete the data referencing contact of id=2 from the phones table

delete from phones where friends_id=2

7 Try to delete the friends of id=2

  delete from friends where friends_id=2

Now, it is gone ! as shown below:

8 Let’s now try to update the id for friend of id=1 to id=5

  update friends set friends_id=5 where friends_id=1

9 Let’s now see the phone table for referential integrity ?

[info]Notice that friends_id attribute within the phones table has been AUTOMATICALLY updated. Because we used :ON UPDATE CASCADE [/info]

Here is a summary for the use of CASCADE, RESTRICT or SET NULL

Questions

1 Suppose that we have set the foreign key as : ON UPDATE RESTRICT. can we change the friends_id from 1 to 5