mysql 主键外键sql_SQL外键VS主键说明了MySQL语法示例-CSDN博客

mysql 主键外键sql

A Foreign Key is a key used to link two tables. The table with the Foreign Key Constraint (aka “child table”) is connected to another table (aka, the “parent table”). The connection is between the child table’s Foreign Key Constraint and the parent table’s Primary Key.

外键是用于链接两个表的键。具有外键约束的表(也称为“子表”)已连接到另一个表(即“父表”)。连接是在子表的外键约束与父表的主键之间。

Foreign Key Constraints are used to help maintain consistency between the tables. For example, if a parent table record is deleted and the child table has records, the system could also delete the child records.

外键约束用于帮助保持表之间的一致性。例如，如果删除了父表记录而子表中有记录，则系统也可以删除子记录。

They also help prevent entering inaccurate data in the child table by requiring that a parent table record exists for every record that is entered in the child table.

它们还通过要求在子表中输入的每个记录都存在一个父表记录，来帮助防止在子表中输入不正确的数据。

使用例 (Example of use)

For this guide we’ll take a closer look at the student (parent) and student contact (child) tables.

对于本指南，我们将仔细查看学生(父母)和学生联系方式(孩子)表。

父表的主键 (The parent table’s primary key)

Note that the student table has a one column primary key of studentID.

请注意，student表具有一列的StudentID主键。

SHOW index FROM student;

+---------+------------+----------+--------------+-------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| student |          0 | PRIMARY  |            1 | studentID   |
+---------+------------+----------+--------------+-------------+
1 row in set (0.00 sec) (some columns removed on the right for clarity)

子表的主键和外键 (The child table’s primary and foreign keys)

The student contact info table has one primary key that is also the studentID. This is because there is a one-to-one relationship between the two tables. In other words, we expect only one student and one student contact record per student.

学生联系信息表具有一个主键，也是学生ID。这是因为两个表之间存在一对一的关系。换句话说，我们期望每个学生只有一名学生和一名学生联系记录。

SHOW index FROM `student-contact-info`;

+----------------------+------------+----------+--------------+-------------+
| Table                | Non_unique | Key_name | Seq_in_index | Column_name |
+----------------------+------------+----------+--------------+-------------+
| student-contact-info |          0 | PRIMARY  |            1 | studentID   |
+----------------------+------------+----------+--------------+-------------+
1 row in set (0.00 sec) (some columns removed on the right for clarity)

SELECT concat(table_name, '.', column_name) AS 'foreign key',
concat(referenced_table_name, '.', referenced_column_name) AS 'references'
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL
AND table_schema = 'fcc_sql_guides_database' 
AND table_name = 'student-contact-info';

+--------------------------------+-------------------+
| foreign key                    | references        |
+--------------------------------+-------------------+
| student-contact-info.studentID | student.studentID |
+--------------------------------+-------------------+
1 row in set (0.00 sec)

使用学生父表和联系子表的示例报告 (Example report using the student parent table and the contact child table)

SELECT a.studentID, a.FullName, a.programOfStudy,
b.`student-phone-cell`, b.`student-US-zipcode`
FROM student AS a
JOIN `student-contact-info` AS b ON a.studentID = b.studentID;

+-----------+------------------------+------------------+--------------------+--------------------+
| studentID | FullName               | programOfStudy   | student-phone-cell | student-US-zipcode |
+-----------+------------------------+------------------+--------------------+--------------------+
|         1 | Monique Davis          | Literature       | 555-555-5551       |              97111 |
|         2 | Teri Gutierrez         | Programming      | 555-555-5552       |              97112 |
|         3 | Spencer Pautier        | Programming      | 555-555-5553       |              97113 |
|         4 | Louis Ramsey           | Programming      | 555-555-5554       |              97114 |
|         5 | Alvin Greene           | Programming      | 555-555-5555       |              97115 |
|         6 | Sophie Freeman         | Programming      | 555-555-5556       |              97116 |
|         7 | Edgar Frank "Ted" Codd | Computer Science | 555-555-5557       |              97117 |
|         8 | Donald D. Chamberlin   | Computer Science | 555-555-5558       |              97118 |
+-----------+------------------------+------------------+--------------------+--------------------+