How to use MySQL Foreign Key Constraints

The foreign key constraint is used to make a relationship between two tables in the MySQL database. It is a very important feature of MySQL to set different types of restrictions on the related tables. To define any foreign key constraint for any table you have to use the primary key of another table. The primary key is a unique key for a table to identify the particular row in the table and when this primary key is used into another table to make one-to-one or one-to-many or many-to-many relationship then it is called a foreign key. The features of foreign keys and how these keys can be used in MySQL tables are shown in this article.

Features of Foreign Key Constraints:

Some important features of foreign key constrain are explained below.

• The data type of the foreign key that is used in the child table must be the same with the data type of the primary key that is used in the parent table to refer the foreign key.
• Any index column or multiple columns can be referenced as a foreign key for the InnoDB table only.
• References privileges or at least one of the privileges of the SELECT, INSERT, UPDATE and DELETE statements is required to create a foreign key.
• A foreign key can be created in two ways. One by using the CREATE statement and another by using the ALTER statement.

Prerequisite:

Before creating a foreign key constraint, you have to create a database and parent table with the primary key. Suppose the database name is ‘library’ and it contains two parent tables named ‘books’ and ‘borrower’. Make a connection with MySQL server using the mysql client and run the following SQL statements to create the database and the tables.

CREATE DATABASE library;
USE library;

CREATE TABLE books (
id INT NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
author varchar(50) NOT NULL,
publisher varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE borrowers (
id VARCHAR(50) NOT NULL,
name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

Define Foreign Key Constraint using CREATE statement

Create a table named ‘book_borrow_info‘ with foreign key constraints by executing the following statement. Here, the book_id field is a foreign key for this table and every value of this field must exist in the id field of books table. books is the parent table and book_borrow_info is the child table. Two restrictions are also set with the foreign key here. These are DELETE CASCADE and UPDATE CASCADE. That means if any primary key will remove or update from the parent table then the corresponding records related to child table related to the foreign key will be removed or the foreign key will be updated.

CREATE TABLE book_borrow_info (
borrow_id VARCHAR(50),
book_id INT,
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
status VARCHAR(15) NOT NULL,
INDEX par_ind (book_id),
PRIMARY KEY (borrow_id, borrow_date),
FOREIGN KEY (book_id) REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=INNODB;

Now, run the following SQL statements to insert some records in both tables. The first INSERT statement will insert four records into books table. The four values of id field of books table will be 1, 2, 3 and 4 for the auto-increment attribute. The second INSERT statement will insert four records into book_borrow_info based on the id value of books table.

INSERT INTO books VALUES
(NULL, 'To Kill a Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
(NULL, 'One Hundred Years of Solitude', 'Garcia Marquez', 'Lutfi Ozkok'),
(NULL, 'A Passage to India', 'Forster, E.M.', 'BBC Hulton Picture Library'),
(NULL, 'Invisible Man', 'Ralph Ellison', 'Encyclopædia Britannica, Inc.');

INSERT INTO book_borrow_info VALUES
('123490', 1, '2020-02-15', '2020-02-25', 'Returned'),
('157643', 2, '2020-03-31', '2020-03-10', 'Pending'),
('174562', 4, '2020-04-04', '2020-04-24', 'Borrowed'),
('146788', 3, '2020-04-10', '2020-01-20', 'Borrowed');

If you try to insert a value in the foreign key field of the child table that does not exist in the primary key field of the parent table then MySQL will generate an error. The following SQL statement will generate an error because the parent table, books doesn’t contain any id value 10.

INSERT INTO book_borrow_info VALUES
('195684', 10, '2020-04-15', '2020-04-30', 'Returned');

After executing the following DELETE statement, when the fourth record will be removed from the books table then the related records from the book_borrow_info table will be removed automatically for the foreign key constraint.

DELETE FROM books WHERE id = 4;
SELECT * from books;
SELECT * from book_borrow_info;

Define Foreign Key Constraint using ALTER statement

At first, insert some records into borrowers table and this table will be defined as parent table in the next ALTER statement.

INSERT INTO borrowers VALUES
('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH', 'wood@gmail.com'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', 'martin@hotmail.com'),
('174562', 'John Innes Archie', '55 Main Road LIVERPOOL L2 3OD', 'john@yahoo.com'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME', 'hanson@gmail.com');

Run the following ALTER statement to set another foreign key constraint for book_borrow_info table to make the relationship with borrowers table. Here, borrow_id is defined as a foreign key for book_borrow_info table.

ALTER TABLE book_borrow_info ADD CONSTRAINT fk_borrower
FOREIGN KEY ( borrow_id ) REFERENCES borrowers (id) ON DELETE CASCADE ON UPDATE RESTRICT;

Now, insert a record into book_borrow_info with valid borrow_id value that exists in id field of borrowers table. 157643 value exists in borrowers table and the following INSERT statement will be executed successfully.

INSERT INTO book_borrow_info VALUES
('157643', 1, '2020-03-10', '2020-03-20', 'Returned');

The following INSERT statement will generate an error message because the id value 195680 does not exist in borrowers table.

INSERT INTO book_borrow_info VALUES
('195680', 1, '2020-04-15', '2020-04-30', 'Returned');

Conclusion:

Defining foreign key constraints properly is a very important task for creating a relational database and manage data among the tables appropriately. Knowing the uses of foreign key constraints is very essential for database designers. I hope this article will help the new database designers to understand the concept of foreign key constraints and apply them properly in their tasks.

from Linux Hint https://ift.tt/3bwkfyJ