Know the basics of foreign keys in MySQL?

Foreign keys can be used to create relationships between tables. Foreign key relationships can be one-to-one or one-to-many. A foreign key matches another field in another table.

• One-to-one relationship - A record in one table will be linked to a record in another table.

• One-to-many relationship - One record will be linked to multiple records in another table.

Below is an example. First, we will create a table. CREATE command is used to create a table.

mysql> create table tblF
    - > (
    - > id int ,
    - > FirstName varchar(100),
    - > FK_PK int
    - > );
Query OK, 0 rows affected (0.57 sec)

Create a second table.

mysql> create table tblP
    - > (
    - > FK_PK int,
    - > LastName varchar(100),
    - > primary key(FK_PK)
    - > );
Query OK, 0 rows affected (0.94 sec)

The following is the syntax for creating foreign keys.

mysql> ALTER table tblF add constraint ConstFK foreign key(FK_PK) references tblP(FK_PK);
Query OK, 0 rows affected (2.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

Use the DESC command to check whether the foreign key has been created.

mysql> DESC tblF;

The following is the output.

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  |     | NULL    |       |
| FirstName | varchar(100) | YES  |     | NULL    |       |
| FK_PK     | int(11)      | YES  | MUL | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

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