Using foreign keys in MySQL

Let us understand how to use foreign keys in MySQL −

InnoDB tables support checking foreign key constraints. No foreign key constraints are required to join the two tables. It can be used when defining the columns that need to be used, for storage engines other than InnoDB. REFERENCES tableName(colName) has no practical effect other than to annotate to the user that the column currently being defined is intended to reference a column in a different table.

MySQL does not check to ensure that 'colName' actually exists in 'tableName', or that 'tableName' itself actually exists.

In the parent table, the foreign key will act as the primary key. Let's see an example of creating a table.

Create child table

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, column ‘StudentFKPK’ is a primary key. We will use the ALTER command to add a foreign key.

The following is the syntax for adding foreign keys.

Syntax

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 above is the detailed content of Using foreign keys in MySQL. For more information, please follow other related articles on the PHP Chinese website!