Mysql 简明教程
MySQL - Foreign Key
在 MySQL 中， Foreign Key 是表中的列（或多列组合），其值与另一个表中主键列的值匹配。因此，使用外键，我们可以将两个表链接在一起。
In MySQL, a Foreign Key is a column (or combination of columns) in a table whose values match the values of a Primary Key column in another table. Thus, using the Foreign key, we can link two tables together.
外键也称为表的引用键，因为它可以引用任何唯一字段。
A Foreign Key is also known as a Referencing key of a table because it can reference any field defined as unique.
除了链接到表外，外键约束还通过防止主键表中的数据更改使到外键表数据的链接无效来确保引用完整性。即，外键防止操作（如“删除表”）消除两个表之间的连接。
In addition to linking to tables, the Foreign Key constraint ensures referential integrity by preventing changes to data in the primary key table from invalidating the link to data in the foreign key table. i.e, a Foreign key prevents operations, like "dropping the table", that would eliminate the connection between two tables.
Creating MySQL Foreign Key
我们可以使用 CREATE TABLE 语句中的 CONSTRAINT…FOREIGN KEY…REFERENCES 关键字在 MySQL 表上创建外键。
We can create a Foreign Key on a MySQL table using the CONSTRAINT… FOREIGN KEY… REFERENCES keyword in the CREATE TABLE statement.
Syntax
以下是向表的列中添加外键约束的语法 −
Following is the syntax to add Foreign Key constraints on a column of a table −
CREATE TABLE table2(
column1 datatype,
column2 datatype,
...
CONSTRAINT constraint_name
FOREIGN KEY (column2)
REFERENCES table1(column1)
);Example
让我们使用 CREATE TABLE 语句创建一个名为 CUSTOMERS 的表 −
Let us create a table named CUSTOMERS using the CREATE TABLE statement −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) UNIQUE,
SALARY DECIMAL (18, 2),
PRIMARY KEY(ID)
);为了说明外键，我们需要两个表，因此让我们再创建一个表 −
To demonstrate the foreign key we need two tables so lets create another table as −
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2),
CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMERS(ID)
);Verification
为了验证是否创建了外键，让我们使用以下语句删除 CUSTOMERS 表而不删除 ORDERS 表 −
To verify if the foreign key is created, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −
DROP TABLE CUSTOMERS;将显示如下错误 −
An error is displayed as follows −
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.Creating Foreign Key on Existing Column
我们还可以在现有表的列上使用 ALTER TABLE…ADD CONSTRAINT 语句创建外键约束。
We can also create a Foreign Key constraint on a column of an existing table using the ALTER TABLE… ADD CONSTRAINT statement.
Syntax
以下是向现有表添加外键约束的语法 −
Following is the syntax to add foreign key constraint on an existing table −
ALTER TABLE table_name2
ADD CONSTRAINT constraint_name
FOREIGN KEY(column_name2)
REFERENCES table_name1(column_name1);Example
以下 MySQL 查询向现有 ORDERS 表的现有列中添加外键约束 FK_CUSTOMERS，该约束引用 CUSTOMERS 表的主键 −
Following is the MySQL query to add a foreign key constraint FK_CUSTOMERS on an existing column of an existing table ORDERS referencing primary key of CUSTOMERS table −
ALTER TABLE ORDERS
ADD CONSTRAINT FK_CUSTOMERS
FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMERS(ID);Output
显示的表结构将在 CUSTOMER_ID 列上包含外键约束，如下所示 −
The table structure displayed will contain a FOREIGN KEY constraint on the CUSTOMER_ID column as shown −
Verification
为了验证我们在 ORDERS 表上创建的外键是否引用 CUSTOMERS 表，让我们使用以下语句删除 CUSTOMERS 表而不删除 ORDERS 表 −
To verify if the foreign key we created on ORDERS is referenced to CUSTOMERS table or not, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −
DROP TABLE CUSTOMERS;将显示如下错误 −
An error is displayed as follows −
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.Dropping MySQL Foreign Key
当表中不再需要时，还可以删除在 MySQL 表上创建的外键。我们可以使用 MySQL 中的 ALTER TABLE…DROP CONSTRAINT 语句执行此操作。
We can also drop the foreign key, created on a MySQL table, whenever it is no longer needed in that table. We can do this using the ALTER TABLE… DROP CONSTRAINT statement in MySQL.
Syntax
以下是从表中删除外键的语法 −
Following is the syntax to drop the foreign key from a table −
ALTER TABLE table_name DROP CONSTRAINT constraint_name;