Creating FOREIGN KEYS with MYSQL INNODB databases

The FOREIGN KEYS are very useful to keep the information integrity on databases. For intance if you have some tables related between them, you can use the FOREIGN KEY to automaticly update or remove data in the child tables whether the parent table has been modified.
Note that the FOREIGN KEY is only available in MYSQL when using InnoDB engine, and the FOREIGN KEY should point to the PRIMARY KEY of another table. In this example I’m using two tables to show how it works.

Table users

The table “users” is the parent table and contains the names and the numeric id of the users.

CREATE TABLE users(
  id INT(10) NOT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample view:

Id name
1 Jimmy
2 Hector

Table phones

The second table “phones” is just for adding the phone of the users. But I want the phones to be automatically deleted when a user has been removed. In order to get that I need to create a reference between the tables using a FOREIGN KEY.

CREATE TABLE phones(
  user_id INT(10) NOT NULL,
  phone VARCHAR(10) NOT NULL,
  PRIMARY KEY (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample view:

user_id phone
1 0876521303
2 087771303

Now just one test. Try to remove one of the users just to check if his associated phone is automatically deleted from the second table.

DELETE FROM users WHERE id=1;

Adding FOREIGN KEYS to an existing tables

If you need to add a FOREIGN KEY to an already existing table, you can do it using the following command:

ALTER TABLE phones ADD FOREIGN KEY (user_id) REFERENCES users(id);

Got something to say? Go for it!