Basic knowledge of foreign keys in MySQL?-Mysql Tutorial-php.cn

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)

Copy after login

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)

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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)

Copy after login

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!