MySQL - 外键

Table of content
上一页
下一页

在 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 约束，如下所示：

字段 类型 空 键 默认值 额外
OID int NO NULL
日期 varchar(20) NO NULL
CUSTOMER_ID int NO MUL NULL
金额 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;

验证

让我们使用以下查询验证外键是否已删除：

DROP TABLE CUSTOMERS;

主键与外键

即使主键和外键都引用同一列，但在它们的工作方式上也存在许多差异。它们列在下面。

主键 外键
主键始终是唯一的。 外键可以重复。
主键不能为 NULL。 外键可以为 NULL。
一个表只能包含一个主键。 每个表可以有多个外键。

使用客户端程序创建外键

我们还可以使用客户端程序在表字段上应用外键约束。

语法

PHP NodeJS Java Python

要在 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 customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(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 customers(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 customers(cust_ID))'
cursorObj.execute(foreign_key_query)

示例

以下是程序：

PHP NodeJS Java Python
$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 \n";
}
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",
}); //Connecting to 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://127.0.0.1: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
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Create table 
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!
打印页面

上一篇
下一篇
广告