Home > Database > Mysql Tutorial > Using foreign keys in MySQL

Using foreign keys in MySQL

王林
Release: 2023-08-31 11:37:02
forward
1366 people have browsed it

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

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, 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);
Copy after login

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!

source:tutorialspoint.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template