MySQL - FOREIGN KEY 外键
在 MySQL 中，外键是指一个表中的列(或多列的组合)，其值与另一个表中主键列的值匹配。因此，使用外键，我们可以将两个表链接在一起。
外键也称为表的引用键，因为它可以引用任何定义为唯一的字段。
具有主键的表称为父表，具有外键的键称为子表。
除了链接到表之外，外键约束还可以通过防止对主键表中数据的更改导致与外键表中数据的链接失效来确保引用完整性。例如，外键可以阻止诸如"删除表"之类的操作，这些操作会切断两个表之间的连接。
创建 MySQL 外键
我们可以在 CREATE TABLE 语句中使用 CONSTRAINT...FOREIGN KEY...REFERENCES 关键字在 MySQL 表上创建外键。
语法
以下是在表的列上添加外键约束的语法 -
CREATE TABLE table2( column1 datatype, column2 datatype, ... CONSTRAINT constraint_name FOREIGN KEY (column2) REFERENCES table1(column1) );
示例
我们使用 CREATE TABLE 语句创建一个名为 CUSTOMERS 的表 -
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) );
为了演示外键，我们需要两个表，因此让我们创建另一个表作为 -
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) );
验证
为了验证外键是否已创建，我们使用以下语句删除 CUSTOMERS 表，但不删除 ORDERS 表 -
DROP TABLE CUSTOMERS;
错误显示如下 -
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.
在现有列上创建外键
我们还可以使用 ALTER TABLE...ADD CONSTRAINT 语句在现有表的列上创建外键约束。
语法
以下是在现有表上添加外键约束的语法 -
ALTER TABLE table_name2 ADD CONSTRAINT constraint_name FOREIGN KEY(column_name2) REFERENCES table_name1(column_name1);
示例
以下 MySQL 查询用于在现有表 ORDERS 的现有列上添加外键约束 FK_CUSTOMERS，该约束引用 CUSTOMERS 表的主键 -
ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMERS FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(ID);
输出
显示的表结构将包含 CUSTOMER_ID 列上的 FOREIGN KEY 约束，如下所示 -
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| OID | int | NO | NULL | ||
| DATE | varchar(20) | NO | NULL | ||
| CUSTOMER_ID | int | NO | MUL | NULL | |
| AMOUNT | decimal(18,2) | YES | NULL |
验证
为了验证我们在 ORDERS 表上创建的外键是否引用了 CUSTOMERS 表，让我们使用以下语句删除 CUSTOMERS 表，但不删除 ORDERS 表 -
DROP TABLE CUSTOMERS;
错误显示如下 -
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.
删除 MySQL 外键
当 MySQL 表不再需要外键时，我们也可以删除在该表上创建的外键。我们可以使用 MySQL 中的 ALTER TABLE...DROP CONSTRAINT 语句来实现这一点。
语法
以下是从表中删除外键的语法 -
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
示例
使用以下 MySQL 查询，我们将从表中删除外键约束 -
ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customers;
验证
让我们通过使用以下查询删除 CUSTOMERS 表来验证外键是否被删除 -
DROP TABLE CUSTOMERS;
主键与外键
尽管主键和外键都引用同一列，但它们的工作方式仍存在许多差异。它们列在下面。
| 主键 | 外键 |
|---|---|
| 主键始终是唯一的。 | 外键可以重复。 |
| 主键不能为 NULL。 | 外键可以为 NULL。 |
| 一个表只能包含一个主键。 | 每个表可以有多个外键。 |
使用客户端程序创建外键
我们也可以使用客户端程序在表字段上应用外键约束。
语法
要通过 PHP 程序在字段上应用外键，我们需要使用 mysqli 函数 query() 在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -
$sql = 'CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))'; $mysqli->query($sql);
要通过 JavaScript 程序在字段上应用外键，我们需要使用 mysql2 库的 query() 函数在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -
sql = `CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID) )`; con.query(sql);
要通过 Java 程序在字段上应用外键，我们需要使用 JDBC 函数 execute() 在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -
String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))"; statement.execute(sql);
要通过 Python 程序在字段上应用外键，我们需要使用 MySQL Connector/Python 的 execute() 函数在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -
foreign_key_query = 'CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))' cursorObj.execute(foreign_key_query)
示例
以下是程序−
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
// printf('Connected successfully.
');
$sql = 'CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30),
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))';
if ($mysqli->query($sql)) {
echo "foreign key column created successfully in customers table
";
}
if ($mysqli->errno) {
printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();
输出
获得的输出如下 -
foreign key column created successfully in customers table
var mysql = require("mysql2");
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
}); //连接到 MySQL
con.connect(function (err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
//creating a column that is foreign key!
sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
con.query(sql);
//describe table details
sql = "DESCRIBE TABLE customers";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
输出
生成的输出如下 -
[
{
id: 1,
select_type: 'SIMPLE',
table: 'customers',
partitions: null,
type: 'ALL',
possible_keys: null,
key: null,
key_len: null,
ref: null,
rows: 1,
filtered: 100,
Extra: null
}
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ForeignKey {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//Create a foreign key in the customer table...!;
String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
statement.execute(sql);
System.out.println("Foreign key created successfully...!");
ResultSet resultSet = statement.executeQuery("DESCRIBE customer");
while (resultSet.next()){
System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
+resultSet.getString(3)+ " "+ resultSet.getString(4));
}
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
输出
获得的输出如下所示 -
Connected successfully...! Foreign key created successfully...! Customer_Id int YES MUL Customer_Name varchar(30) YES
import mysql.connector
#建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
cursorObj = connection.cursor()
# 创建表
foreign_key_query = '''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'''
cursorObj.execute(foreign_key_query)
connection.commit()
print("Foreign key column is created successfully!")
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
Foreign key column is created successfully!
