MySQL and foreign keys

When I talk about SQL databases, I always mean a set of tables with some relations between them. Relations in the databases are possible due to foreign keys. In the post I’m going to make a quick overview of the set up of foreign keys in MySQL using Toad MySQL. MySQL has strict standards, so Toad MySQL shouldn’t create many restrictions for porting the steps to any other MySQL development tool.

Theory

Creation of foreign key implies existing of at least two tables (InnoDB) in the database. One of the tables should be a parent table and the other a child. The child table must have a field corresponding to the parent’s field and have the same data type and length as parent’s one has.

CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Foreign key always leads from a child table to a parent table. That’s why both tables have fields with similar properties.
After the short theoretical introduction I’m going to proceed to practice.

Creation of foreign key in Toad MySQL

In the previous paragraph I have shown how to create two tables which we will use later on. Firstly, I’m going to create a new index in the child table for the parent_id column. To do this I need to click on the appropriate button in Toad MySQL, and select the column for the new indexing:

And after that, script for the table will be changed to:

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id_index` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The second step is to declare the new constraint for the child table:

The constraint contains two main components:

1. The representation of the parent’s column for the foreign key (parent_id) in the child table;
2. The destination column in the parent table (id).

And after that, script for the table will changed to:

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id_index` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Also you can specify behavior of the foreign key on delete and on update actions in the constraint dialog window.
In the end we get two tables coupled with each other by foreign key.