Basic knowledge of foreign keys in MySQL?

Foreign key is a type of constraint that can be used to maintain integrity between tables. If we create a foreign key for one table, then that table is called child table and the second table is called parent table.

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

Create subtable.

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

Create 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 "StudentFKPK" column is the primary key. We will add foreign keys using ALTER command.

The following is the syntax for adding foreign keys.

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 following 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 for checking whether foreign key constraints exist.

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 query below.

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';

This 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 called StudentFKPK and CONSTRAINT_NAME is StudCollegeConst.

The above is the detailed content of Basic knowledge of foreign keys in MySQL?. For more information, please follow other related articles on the PHP Chinese website!