sql外键_SQL外键

sql外键

When a database table is designed an important factor is to make sure that the table is scalable enough and is normalized at the same time. In order to make sure that the tables are well linked and normalized we use SQL foreign keys.

设计数据库表时，重要的因素是确保该表具有足够的可伸缩性并同时进行规范化。 为了确保表之间的链接和规范化，我们使用SQL外键。

SQL外键 (SQL Foreign Key)

A foreign key is defined by a column that matches a column of another table. The foreign key constraint is used to make sure that data referential integrity is maintained.

外键由与另一个表的列匹配的列定义。 外键约束用于确保保持数据引用完整性。

In this tutorial, we will try to understand the following topics.

在本教程中，我们将尝试理解以下主题。

1. Create a foreign key
   创建外键
2. Add a foreign key
   添加外键
3. Drop foreign key
   删除外键

1.创建一个外键 (1. Create a Foreign Key)

The below-mentioned syntax will help in understanding how to create a foreign key in the child table. A child table is a table where the foreign key is defined from the parent table.

下面提到的语法将有助于理解如何在子表中创建外键。 子表是从父表定义外键的表。

Syntax: –

句法： -

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

Please find below the details of the syntax mentioned above,

请在下面找到上述语法的详细信息，

Constraint: – This keyword allows to specify a name for the foreign key constraint.

约束：–此关键字允许为外键约束指定名称。

Foreign Key: – This keyword specifies the columns in the child table that refers to the primary key of the parent table.

外键：–此关键字指定子表中引用父表主键的列。

References: – It specifies the parent table and its column that is used as a foreign key.

参考：–它指定父表及其用作外键的列。

On Delete: – On delete specifies the action that will be performed on the deletion of a row of the parent table.

On Delete（删除时）：– On Delete（删除时）指定在删除父表的一行时要执行的操作。

On Update: – On update specifies the action that will be performed on the updating a row of the parent table.

更新时：–更新时指定将在更新父表的行时执行的操作。

We will create a new table using the following

我们将使用以下内容创建一个新表

CREATE TABLE school(
sch_id int not null auto_increment primary key,
sch_name varchar(255) not null,
sch_description text
) ;

CREATE TABLE student(
std_id int not null auto_increment primary key,
std_name varchar(355) not null,
std_class varchar(355) not null,
sch_id int not null,
FOREIGN KEY fk_sch(sch_id)
REFERENCES school(sch_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);

Using the above query we will be creating two tables School and Student. School is the parent table for the Student table and has a foreign key reference for the same.

使用上面的查询，我们将创建两个表School和Student。 School是Student表的父表，并且具有相同的外键引用。

After executing the above query following will be the property of the Student table.

执行完上面的查询后，以下将是Student表的属性。

Add Foreign Key MySQL

添加外键MySQL

2.添加外键 (2. Add a Foreign Key)

The below-mentioned syntax will help in understanding how to add a foreign key in the child table. Using Alter keyword we can add a foreign key to an existing table.

下面提到的语法将有助于理解如何在子表中添加外键。 使用Alter关键字，我们可以将外键添加到现有表中。

Syntax: –

句法： -

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

We will try to add a new foreign key to the Student table which we have created in the previous example.

我们将尝试将新的外键添加到在上一个示例中创建的Student表中。

CREATE TABLE class(
cls_id int not null auto_increment primary key,
cls_name varchar(255)
);

ALTER TABLE student
ADD COLUMN cls_id int not null;
ALTER TABLE student
Add FOREIGN KEY fk_cls(cls_id)
REFERENCES class(cls_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Using the above query we will be creating a new table Class and will be adding a foreign key to the Student table

使用上面的查询，我们将创建一个新的表Class并将一个外键添加到Student表中

After executing the above query following will be the property of the Student table.

执行完上面的查询后，以下将是Student表的属性。

Alter Foreign Key MySQL

修改外键MySQL

3.删除外键 (3. Drop a Foreign Key)

The below-mentioned syntax will help in understanding how to drop a foreign key in the child table. Using Alter keyword we can drop a foreign key to an existing table.

下面提到的语法将有助于理解如何在子表中删除外键。 使用Alter关键字，我们可以将外键拖放到现有表中。

Syntax: –

句法： -

ALTER table_nameDROP FOREIGN KEY constraint_name;

We will try to drop a foreign key from the Student table which we have created in the previous example.

我们将尝试从上一个示例中创建的Student表中删除外键。

ALTER TABLE student
DROP FOREIGN KEY fk_cls;

Using the above query we will be dropping the foreign key of Student table

使用上面的查询，我们将删除Student表的外键

After executing the above query following will be the property of the Student table.

执行完上面的查询后，以下将是Student表的属性。

Drop Foreign Key MySQL

删除外键MySQL

删除级联中的外键如何工作？ (How Foreign Key On Delete Cascade Works?)

We can use the following query to set the “ON DELETE CASCADE” option for the foreign key constraint. In this case, if the corresponding row in the parent table is deleted, the rows in the child table will also get deleted.

我们可以使用以下查询为外键约束设置“ ON DELETE CASCADE”选项。 在这种情况下，如果删除父表中的相应行，则子表中的行也将被删除。

Let’s add it to the student table.

让我们将其添加到学生表中。

ALTER TABLE student Add FOREIGN KEY fk_sch(sch_id) REFERENCES school(sch_id) ON DELETE CASCADE;

We will run the following command to insert data into School table.

我们将运行以下命令将数据插入School表。

School Table:

学校表：

INSERT INTO `test`.`school`
(
`sch_name`,
`sch_description`)
VALUES("TestSchool1","School for test 1"),("TestSchool2","School for test 2"),("TestSchool3","School for test 3");

Student Table:

学生桌：

INSERT INTO `test`.`student`
(
`std_name`,
`std_class`,
`sch_id`,
`cls_id`)
VALUES("Student 1","Class 1",1,1),("Student 2","Class 1",2,1)("Student 3","Class 1",1,1),("Student 4","Class 1",1,1),("Student 5","Class 1",3,1),("Student 6","Class 1",3,1);

Now we will try to delete a row from the child table using the following command.

现在，我们将尝试使用以下命令从子表中删除一行。

DELETE FROM `test`.`student`
WHERE sch_id = 2

This will only delete the entry from Child table where sch_id = 2 and the parent table will remain unchanged.

这只会从sch_id = 2的子表中删除该条目，而父表将保持不变。

Student Table After Delete

删除后的学生表

Now we will try to delete an entry from the parent table and we will see the changes on child table for the same.

现在，我们将尝试从父表中删除一个条目，我们将在子表中看到相同的更改。

We will execute the following command for delete row from the parent table.

我们将执行以下命令从父表中删除行。

DELETE FROM `test`.`school`
WHERE sch_id=3;

Following will be the result post execution of delete command for the parent table. The rows in the child table will also get deleted.

以下是父表的delete命令执行后的结果。 子表中的行也将被删除。

Student Table After Delete Sch Id 3

删除Sch ID 3后的学生表

结论 (Conclusion)

SQL Foreign keys help us in creating a relationship between tables. It also lets us specify what will happen if the foreign key in the parent table is updated or deleted.

SQL外键可帮助我们在表之间创建关系。 它还允许我们指定如果父表中的外键被更新或删除，将会发生什么。

翻译自: https://www.journaldev.com/29513/sql-foreign-key

sql外键