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.
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.
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.
(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.
('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.
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.
('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH', '[email protected]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', '[email protected]'),
('174562', 'John Innes Archie', '55 Main Road LIVERPOOL L2 3OD', '[email protected]'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME', '[email protected]');
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.
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.
('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.
('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.
