Basics of Foreign Keys in MySQL?

Foreign Keys is a type of constraint that can be used in order to maintain integrity between tables. If we create a foreign key for a table, then this table is called the child table while the second table will be called a parent table.

In the parent table, the foreign key will act as a primary key. Let us create a table.

Creating the child table.

mysql> create table StudentEnrollment
   -> (
   -> StudentId int,
   -> StudentName varchar(200),
   -> StudentFKPK int
   -> );
Query OK, 0 rows affected (0.91 sec)

Creating the parent table

mysql> create table College
   -> (
   -> StudentFKPK int,
   -> CourseId int,
   -> CourseName varchar(200),
   -> CollegeName varchar(200),
   -> primary key(StudentFKPK)
   -> );
Query OK, 0 rows affected (0.46 sec)

In the parent table, the column ‘StudentFKPK’ is a primary key. We will use the ALTER command to add a foreign key.

The following is the syntax to add a foreign key.

ALTER table yourChildTableName add constraint anyConstraintName
foreign key(primary key column name for parent table) 
references College(primary key column name for parent table);

The following is the implementation of the above syntax in the below query.

mysql> alter table StudentEnrollment add constraint StudCollegeConst foreign key(StudentFKPK) references College(StudentFKPK);
Query OK, 0 rows affected (1.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

Syntax to check if the foreign key constraint is present.

SELECT  TABLE_NAME, COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NA FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHER  REFERENCED_TABLE_NAME = 'yourParentTableName or your referencedTableName';

The above syntax is implemented in the below query.

mysql> SELECT
   ->   TABLE_NAME,
   ->   COLUMN_NAME,
   ->   CONSTRAINT_NAME,
   ->   REFERENCED_TABLE_NAME,
   ->   REFERENCED_COLUMN_NAME
   ->   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   ->   WHERE
   ->   REFERENCED_TABLE_NAME = 'College';

Here is the output.

+-------------------+-------------+------------------+-----------------------+------------------------+
| TABLE_NAME        | COLUMN_NAME | CONSTRAINT_NAME  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------------+-------------+------------------+-----------------------+------------------------+
| StudentEnrollment | StudentFKPK | StudCollegeConst | College               | StudentFKPK            |
+-------------------+-------------+------------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)

We have a column named StudentFKPK and CONSTRAINT_NAME is StudCollegeConst.