MySQL: “Foreign Keys” Used in an Example

Below we will show some simple examples of how to set up foreign keys and how they work in MySQL. In our examples, we will reference the parent and child tables below. The tables labelled “Customer” and “Contact” represent the parent and child tables, respectively.

There are basically two ways to define foreign keys in MySQL:

1. Defining Foreign Keys with CREATE TABLE
2. Defining Foreign Keys with ALTER TABLE

In addition to these examples, we have also included a discussion of what foreign key constraints are and how they are formatted, with examples as well.

A foreign key is a field (or a set of fields) in a table that uniquely identifies a row of another table. The table in which the foreign key is defined is called the “child table” and it (often) refers to the primary key in the parent table.

Foreign key constraints can then be used to define how data integrity is enforced between two tables (e.g., when a table row is deleted or updated).

If done correctly, the link between the primary key and the foreign key will always be maintained, so your database will never be left with orphaned records in the child table (e.g., leftover contact information tied to a removed customer). The following simplified (and not fully-normalized) customer database example helps us visualize these foreign key concepts.

Go into the MySQL console by typing the mysql command with the correct user and password arguments. If needed, type “man mysql” to get more information.

mysql -u -p

Create a database and start using it:

CREATE DATABASE testdb;
use testdb;

Creating the Tables

Now create the two tables:

CREATE TABLE customer (
  id INT NOT NULL AUTO_INCREMENT,
  firstname varchar(50) NOT NULL,
  lastname varchar(50) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE contact (
  id INT,
  customer_id INT,
  info varchar(50) NOT NULL,
  type varchar(50) NOT NULL,
  INDEX par_ind (customer_id),
  CONSTRAINT fk_customer FOREIGN KEY (customer_id)
  REFERENCES customer(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
) ENGINE=INNODB;

Verifying the Table Structure

Let’s see what our database structure looks like:

SHOW TABLES;
+——————+
| Tables_in_testdb |
+——————+
| contact |
| customer |
+——————+

DESCRIBE customer;
+———–+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| firstname | varchar(50) | NO | | NULL | |
| lastname | varchar(50) | NO | | NULL | |
+———–+————-+——+—–+———+—————-+

DESCRIBE contact;
+————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| customer_id | int(11) | YES | MUL | NULL | |
| info | varchar(50) | NO | | NULL | |
| type | varchar(50) | NO | | NULL | |
+————-+————-+——+—–+———+——-+

Note the MUL value in the Key column of the contact table. This tells us the customer_id field is the first column of a non-unique index (meaning it can have multiple rows with the same value).

Adding Data to the Table

Now we can fill our two database tables with some sample data. First, the customer table:

INSERT INTO customer (firstname, lastname) VALUES
(‘Elaine’, ‘Stevens’),
(‘Mary’, ‘Dittman’),
(‘Skip’, ‘Stevenson’);

Our customer table now looks like this:

SELECT * FROM customer;
+—-+———–+———–+
| id | firstname | lastname |
+—-+———–+———–+
| 1 | Elaine | Stevens |
| 2 | Mary | Dittman |
| 3 | Skip | Stevenson |
+—-+———–+———–+

Some sample data for the contact table:

INSERT INTO contact (customer_id, info, type) VALUES
(‘1’, ‘111–111–111’, ‘work’ ),
(‘1’, ‘111–111–123’, ‘home’),
(‘1’, ‘elaine@example.com’, ‘email’),
(‘2’, ‘222–222–222’, ‘work’),
(‘2’, ‘mary@example.com’, ‘email’),
(‘2’, ‘mary2@example.com’, ‘email’),
(‘3’, ‘skip@example.com’, ‘email’);

Our contact table now looks like this:

SELECT * FROM contact;
+——+————-+——————–+——-+
| id | customer_id | info | type |
+——+————-+——————–+——-+
| NULL | 1 | 111–111–111 | work |
| NULL | 1 | 111–111–123 | home |
| NULL | 1 | elaine@example.com | email |
| NULL | 2 | 222–222–222 | work |
| NULL | 2 | mary@example.com | email |
| NULL | 2 | mary2@example.com | email |
| NULL | 3 | skip@example.com | email |
+——+————-+——————–+——-+

Now that we have the sample data, let’s see how foreign keys help preserve data integrity.

Deleting Referential Data (to See How Data Integrity is Preserved)

Earlier we defined the foreign key constraint as:

“FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE”

This means that when we delete a specific customer, the related contact table rows should also be deleted. Also, ON UPDATE CASCADE will cascade any updates on the parent table to referenced fields in the child table (in this case, customer_id).

Let’s first test deleting a customer:

DELETE FROM customer WHERE firstname = ‘Skip’;

When we look at our tables again, we see that both tables were changed as required, since all the contact rows for Skip were also removed:

SELECT * FROM customer;
+—-+———–+———-+
| id | firstname | lastname |
+—-+———–+———-+
| 1 | Elaine | Stevens |
| 2 | Mary | Dittman |
+—-+———–+———-+

SELECT * FROM contact;
+——+————-+——————–+——-+
| id | customer_id | info | type |
+——+————-+——————–+——-+
| NULL | 1 | 111–111–111 | work |
| NULL | 1 | 111–111–123 | home |
| NULL | 1 | elaine@example.com | email |
| NULL | 2 | 222–222–222 | work |
| NULL | 2 | mary@example.com | email |
| NULL | 2 | mary2@example.com | email |
+——+————-+——————–+——-+

Updating Referential Data (to See How Data Integrity is Preserved)

Now let’s update the customer_id of Elaine, to test the ON UPDATE CASCADE:

UPDATE customer SET id=7 WHERE firstname=’Elaine’;

The needed changes have now been made to both tables:

select * from customer;
+—-+———–+———-+
| id | firstname | lastname |
+—-+———–+———-+
| 7 | Elaine | Stevens |
| 22 | Mary | Dittman |
+—-+———–+———-+

select * from contact;
+——+————-+——————–+——-+
| id | customer_id | info | type |
+——+————-+——————–+——-+
| NULL | 7 | 111–111–111 | work |
| NULL | 7 | 111–111–123 | home |
| NULL | 7 | elaine@example.com | email |
| NULL | 22 | 222–222–222 | work |
| NULL | 22 | mary@example.com | email |
| NULL | 22 | mary2@example.com | email |
+——+————-+——————–+——-+

It is also possible to add foreign keys to tables after they have already been created. Instead of doing the table creation in one step, as we did before, we can also do it in multiple steps.

Creating & Then Altering the Tables

CREATE TABLE customer (
  id INT NOT NULL AUTO_INCREMENT,
  firstname varchar(50) NOT NULL,
  lastname varchar(50) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE contact (
  id INT,
  customer_id INT,
  info varchar(50) NOT NULL,
  type varchar(50) NOT NULL
) ENGINE=INNODB;

ALTER TABLE contact ADD INDEX par_ind ( customer_id );
ALTER TABLE contact ADD CONSTRAINT fk_customer
FOREIGN KEY ( customer_id ) REFERENCES customer ( id ) ON DELETE CASCADE ON UPDATE RESTRICT;

Note that you can’t change an existing foreign key. You will first have to remove the existing foreign key by its symbol name and then you can define a new foreign key as we did previously.

ALTER TABLE contact DROP FOREIGN KEY fk_customer;

Foreign keys are integral to good database management. If you are now interested in looking at more advanced examples of their use, see the Examples of Foreign Key Clauses section here.

On that page is an example in which a “product_order” table has foreign keys for two other tables. One foreign key references a two-column index in the “Product” table. The other references a single-column index in the “Customer” table.

Other interesting foreign key examples can be found here.

Foreign key constraints are used to keep the data in your tables consistent when either removing (ON DELETE) or updating (ON UPDATE) table row data.

The syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_column_name, …)
REFERENCES parent_table_name (index_column_name,…)
[ON DELETE reference_option][ON UPDATE reference_option]

The “CONSTRAINT [symbol]” part is optional and can be used to define a name for the foreign key. This is useful for removing foreign keys by their symbol name and also for producing clearer error logging.

The index_name value represents a foreign key ID and is ignored if there is already an explicitly defined index on the child table that can support the foreign key. One or more columns can be used to uniquely identify the foreign key.

Reference Option

Used by both ON DELETE and ON UPDATE, reference_option can point to one of the following:

CASCADE:

This command deletes or updates the row from the parent table, and automatically deletes or updates the matching rows in the child table.

RESTRICT:

This rejects the DELETE or UPDATE operations for the parent table. Specifying RESTRICT is the same as omitting the ON DELETE or ON UPDATE clause. In other words, rejection is the default action in MySQL.

NO ACTION:

A keyword from standard SQL, NO ACTION is essentially equivalent to the RESTRICT command in MySQL.

SET NULL:

Finally, the SET NULL command deletes or updates the row from the parent table, and sets the foreign key column (or columns) in the child table to NULL. This action can be useful in situations where the rows in the child table should not be deleted when the parent table row is removed.

Photo Sources: www.mvpcoo.com