MySQL的主键和外键
MySQL是世界上使用最广泛的开源关系数据库管理系统。
许多Web应用程序都使用MySQL。
在阅读本文时，实际上是在间接使用MySQL数据库。
如果我们是关系数据库管理系统的新手，那么我建议阅读以下文章以获取有关它的想法。
阅读：什么是关系数据库管理系统
数据库的主要职责是存储有关不同对象或者实体以及它们之间的关系的信息。
例如，一个教育机构可能会存储有关学生和所提供程序的详细信息。
程序和学生之间可能存在某种关系。
然后可以将这种关系称为注册。
如果我们不熟悉数据库，则可以将数据库表视为具有不同列和多行的excel工作表。
列将包含不同的属性，这些属性将描述该表中的一行。
让我们以学生和程序表为例。
属性（表中的列）我们将一个实体与另一个实体区分开（另一行）。
students表中的一行将包含有关特定学生的所有详细信息，而courses表中的一行将描述程序详细信息。
当我们说出学生表时，该表应包含诸如“名字”，“姓氏”，“电话号码”，“电子邮件地址”等列。
我们可以考虑使用“名称”属性（列）来帮助将一个学生与另一个学生区分开（即：唯一地标识一个特定的学生） 。
但是，这听起来不是一个好的解决方案。
因为两个不同的学生（行）可能具有相同的名称。
为了能够唯一地识别一个学生，我们需要有一个属性（列），该属性对于数据库表中的每个学生条目（行）都必须是唯一的。
此属性（列）在数据库中称为主键。
我们还可以考虑“学生”表中的“电话号码”列来唯一地标识一名特定学生，否则我们也可以考虑电子邮件地址。
选择唯一地标识一个条目/实体（行）的主键（属性或者列）应取决于我们对它是非空的信心，并且对每个单独的实体（行）都是唯一的。
柱子。
主键绝不能为NULL。
例如，如果某个特定的学生不提供电子邮件地址或者电话号码，则我们不能将它们中的任何一个用作我们的主键。
为了使对主键的选择更加容易，我们实际上可以从我们侧面创建/添加一个人工属性（列），该属性将被定义为唯一。
然后，可以将我们创建的该人工密钥（列）用作主密钥。
通常，此人工列将包含数字，这些数字将为添加到表中的每个条目（行）自动自动递增。
例如，在我们创建的人工列中，第一行的条目为1，第二行的条目为2，第三行的条目为3，依此类推。
因此，我们可以百分百确定它是唯一且非空的。
| s_id | s_firstname | s_lastname | s_email |
|---|---|---|---|
| 1 | shankar | bhat | [email protected] |
| 2 | Venkat | Rao | [email protected] |
| 3 | mohan | nair | [email protected] |
| 4 | abhijeet | Patel | [email protected] |
上面显示的是我们的“学生”表，其中有5个不同的学生。
在我们的示例中有4列。
在这些列中，s_id列是用作主键的人工列。
该列中的值是为表中每个单独的学生自动创建的。
由于每个条目的条目都会自动递增，因此每个学生的条目将是100％唯一的。
我们可以使用s_id列作为我们的主键，以从表中唯一地识别一名特定的学生。
让我们在mysql数据库中实现相同的示例。
现在，我要连接到测试MySQL服务器，并运行几个命令，以实现我们上面看到的相同结果。
mysql> create database university; Query OK, 1 row affected (0.00 sec)
在上面的show MySQL命令中，我们正在创建一个名为“大学”的数据库。
现在，让我们在该数据库中创建一个students表。
mysql> use university; Database changed mysql> create table students (s_id INT(10) NOT NULL AUTO_INCREMENT, s_firstname VARCHAR(30) NOT NULL, s_lastname VARCHAR(30) NOT NULL, s_email VARCHAR(40), PRIMARY KEY (s_id)); Query OK, 0 rows affected (0.07 sec)
现在，将几个条目添加到我们新创建的“ students”表中。
mysql> insert into students (s_firstname,s_lastname,s_email) values ('Shankar', 'Bhat', '[email protected]');
Query OK, 1 row affected (0.02 sec)
mysql> insert into students (s_firstname,s_lastname,s_email) values ('Venkat', 'Rao', '[email protected]');
Query OK, 1 row affected (0.00 sec)
mysql> insert into students (s_firstname,s_lastname,s_email) values ('Mohan', 'Nair', '[email protected]');
Query OK, 1 row affected (0.06 sec)
mysql> insert into students (s_firstname,s_lastname,s_email) values ('Abhijeet', 'Patel', '[email protected]');
Query OK, 1 row affected (0.03 sec)
mysql> select * from students;
+------+-------------+------------+---------------------+
| s_id | s_firstname | s_lastname | s_email |
+------+-------------+------------+---------------------+
| 1 | Shankar | Bhat | [email protected] |
| 2 | Venkat | Rao | [email protected] |
| 3 | Mohan | Nair | [email protected] |
| 4 | Abhijeet | Patel | [email protected] |
+------+-------------+------------+---------------------+
在示例中添加为主键的自动生成的人工列也称为代理键。
现在，我们可以使用表中的s_id列（即我们的主键）来唯一地标识一个特定的学生，如下所示。
mysql> select * from students where s_id='3'; +------+-------------+------------+-------------------+ | s_id | s_firstname | s_lastname | s_email | +------+-------------+------------+-------------------+ | 3 | Mohan | Nair | [email protected] | +------+-------------+------------+-------------------+ 1 row in set (0.00 sec)
我们还可以通过使用表中的多个列来创建主键。
让我们在同一示例中添加另一列（让我们在存储学生电话号码的同一张表中添加一列。
）
正如我们已经讨论的那样，主键应该能够唯一地标识表中的一个特定记录（行）。
两个学生可以使用相同的名字，两个学生可以使用相同的姓氏，两个学生也可以使用相同的电话号码（如果他们都来自同一个房子），这是完全有可能的。
但是，两个学生拥有相同的电话号码和相同的名字的可能性较小。
因此，名字和电话号码结合在一起将可以唯一地标识特定的学生。
因此，在这种情况下，我们可以构造一个主键而无需使用代理键（我们之前添加的人工自动增量列）。
| s_id | s_firstname | s_lastname | s_email | s_phone |
|--- |--- |--- |--- |
| 1 | shankar | bhat | [email protected] | 7303075409 |
| 2 | Venkat | Rao | [email protected] | 7404076894 |
| 3 | mohan | nair | [email protected] | 7404076892 |
| 4 | abhijeet | Patel | [email protected] | 7404076991 |
| 5 | manoj | nair | [email protected] | 7404076892 |
现在，让我们使用上面的students表创建大学数据库。
mysql> create database university; Query OK, 1 row affected (0.00 sec)
现在让我们创建带有两列作为主键的Student表（即s_firstname和s_phone的组合）。
它也称为复合密钥。
mysql> create table students (s_firstname VARCHAR(30) NOT NULL, s_lastname VARCHAR(30) NOT NULL, s_email VARCHAR(40), s_phone BIGINT(10) NOT NULL, PRIMARY KEY (s_phone, s_firstname)); Query OK, 0 rows affected (0.09 sec)
现在让我们添加一些条目（与上表中提到的完全相同的条目。
）
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Shankar', 'Bhat', '[email protected]', '7303075409');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Venkat', 'Rao', '[email protected]', '7404076894');
Query OK, 1 row affected, 1 warning (0.08 sec)
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Mohan', 'Nair', '[email protected]', '7404076892');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Abhijeet', 'Patel', '[email protected]', '7404076991');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Manoj', 'Nair', '[email protected]', '7404076892');
Query OK, 1 row affected, 1 warning (0.06 sec)
现在，我们已经准备好带有复合主键的完整表（完整表条目如下所示。
）
mysql> select * from students; +-------------+------------+---------------------+------------+ | s_firstname | s_lastname | s_email | s_phone | +-------------+------------+---------------------+------------+ | Shankar | Bhat | [email protected] | 7303075409 | | Manoj | Nair | [email protected] | 7404076892 | | Mohan | Nair | [email protected] | 7404076892 | | Venkat | Rao | [email protected] | 7404076894 | | Abhijeet | Patel | [email protected] | 7404076991 | +-------------+------------+---------------------+------------+
现在，我们可以使用电话号码和名字来唯一标识学生，如下所示。
mysql> select * from students where s_firstname='Mohan' AND s_phone='7404076892'; +-------------+------------+-------------------+------------+ | s_firstname | s_lastname | s_email | s_phone | +-------------+------------+-------------------+------------+ | Mohan | Nair | [email protected] | 7404076892 | +-------------+------------+-------------------+------------+ 1 row in set (0.00 sec)
此外，数据库不会允许我们使用相同的名字和相同的电话号码创建另一个条目。
运行以下查询应引发主键错误。
mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Manoj', 'Pillai', '[email protected]', '7404076892');
ERROR 1062 (23000): Duplicate entry '7404076892-Manoj' for key 'PRIMARY'
在上面显示的条目中，表中已经存在名字和电话号码，因此，它会引发“键'PRIMARY'的重复条目”错误。
我们实际上可以使用MySQL中的ALTER TABLE语句在现有表上添加主键，如下所示。
mysql> ALTER TABLE students ADD PRIMARY KEY (s_id);
我们还可以使用以下命令在现有表上添加组合主键（这两个示例均使用students表，以及students表中可用的列。
在这种情况下，我们必须将其替换为对应的表名和列。
）
mysql> ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (s_phone,s_firstname);
什么是SQL中的外键？
用简单的话来说就是“外键是一种可以将两个不同的表链接在一起的东西”。
“它是一个表中的一列，链接到另一个表的主键”。
让我们采用在学习主键时创建的同一张学生表（不是我们为复合主键创建的students表，而是我们使用名为s_id的自动生成的人工列创建的students表）。
学生表如下所示。
mysql> select * from students; +------+-------------+------------+---------------------+ | s_id | s_firstname | s_lastname | s_email | +------+-------------+------------+---------------------+ | 1 | Shankar | Bhat | [email protected] | | 2 | Venkat | Rao | [email protected] | | 3 | Mohan | Nair | [email protected] | | 4 | Abhijeet | Patel | [email protected] | +------+-------------+------------+---------------------+ 4 rows in set (0.00 sec)
现在，在同一大学数据库中创建一个名为“程序”的新表。
该表将列出大学为学生提供的程序。
mysql> create table courses (c_id INT(10) NOT NULL AUTO_INCREMENT, c_name VARCHAR(30) NOT NULL, PRIMARY KEY (c_id)); Query OK, 0 rows affected (0.07 sec)
让我们使用MySQL INSERT语句向我们新创建的程序表中添加一些程序，如下所示。
mysql> insert into courses (c_name) values ('Computer Science');
Query OK, 1 row affected (0.05 sec)
mysql> insert into courses (c_name) values ('Economics');
Query OK, 1 row affected (0.03 sec)
mysql> insert into courses (c_name) values ('Arts');
Query OK, 1 row affected (0.07 sec)
mysql> insert into courses (c_name) values ('Chemistry');
Query OK, 1 row affected (0.03 sec)
mysql> insert into courses (c_name) values ('Astro Physics');
Query OK, 1 row affected (0.05 sec)
最后，我们的程序表如下所示。
mysql> select * from courses; +------+------------------+ | c_id | c_name | +------+------------------+ | 1 | Computer Science | | 2 | Economics | | 3 | Arts | | 4 | Chemistry | | 5 | Astro Physics | +------+------------------+ 5 rows in set (0.00 sec)
现在让我们创建另一个名为enrollment的表。
该表将包含学生的入学详细信息。
入学表将链接到学生表以及程序表。
这是因为报名只不过是学生和程序表的组合。
学生选择大学中的特定程序，这将在注册表中创建一个条目。
mysql> create table enrollment (e_id INT(10) NOT NULL AUTO_INCREMENT, e_StudentID integer, e_CourseID integer, e_year YEAR, PRIMARY KEY (e_id), FOREIGN KEY (e_StudentID) REFERENCES students(s_id), FOREIGN KEY (e_CourseID) REFERENCES courses(c_id)); Query OK, 0 rows affected (0.10 sec)
在上面显示的MySQL查询中，我们正在创建一个具有4个属性（列）的注册表。
首先是e_id（自动生成的人工列，用于唯一标识一个特定的注册。
）。
第二列是e_StudentID列，它实际上引用了students表中的s_id列（因为Students表中的s_id列将唯一地标识一个特定的学生。
）。
同样，第三列e_CourseID引用程序表中的c_id列（程序表中的c_id列是主键，它将唯一地标识一门特定程序）。
e_year的最后一列仅记录特定学生注册程序的年份。
在e_CourseID和e_StudentID列中，我们只能在程序和学生表的c_id和s_id列中包含有效且存在的条目。
与主键s_id和c_id的这种外部关系将保持注册表中数据的完整性。
现在，让我们在注册表中添加一些条目，如下所示。
mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (1, 3, 2015); Query OK, 1 row affected (0.13 sec) mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (2, 4, 2015); Query OK, 1 row affected (0.04 sec) mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (3, 2, 2015); Query OK, 1 row affected (0.01 sec) mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (4, 1, 2015); Query OK, 1 row affected (0.04 sec)
请密切注意我们在注册表中插入的条目。
我们为e_StudentID和e_CourseID提供的值是有效的条目，这些条目可在学生和程序表的s_id和c_id表中使用。
在注册表中添加条目，而其值在“学生和程序”表的主键中不存在，将产生错误，如下所示。
mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (5, 1, 2015); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`enrollment`, CONSTRAINT `enrollment_ibfk_1` FOREIGN KEY (`e_StudentID`) REFERENCES `students` (`s_id`))
此错误是因为为e_StudentID提供的5值无效。
它不在学生表中（请参阅前面显示的学生表。
它只有4个学生的4个条目。
因此，只有4个有效学生可供我们在注册表中使用。
）
我们的注册表包含我们创建的所有有效条目，如下所示。
mysql> select * from enrollment; +------+-------------+------------+--------+ | e_id | e_StudentID | e_CourseID | e_year | +------+-------------+------------+--------+ | 1 | 1 | 3 | 2015 | | 3 | 2 | 4 | 2015 | | 4 | 3 | 2 | 2015 | | 5 | 4 | 1 | 2015 | +------+-------------+------------+--------+ 4 rows in set (0.00 sec)
我们在e_StudentID和e_CourseID中看到的值是“学生和程序”表中的主键。
外键可用于保护表的完整性。
它还将保持表之间的链接处于完美和有效状态。
