MySQL - 复合键

MySQL：复合主键

复合主键是由表中两个或多个列组成的主键。当没有单个列本身足以保证唯一性时，它用于唯一标识每条记录。复合键中列的组合对于每行都必须是唯一的。

复合键的特点

一个表只能有一个 PRIMARY KEY（主键），它可以是单个列，也可以是多个列的复合键。
作为复合主键一部分的任何列都不能包含 NULL 值。
键中所有列的组合值对于表中的每一行都必须是唯一的。
在复合键中，单个列可以包含重复值，只要整体组合保持唯一即可。

创建复合键

复合键是在创建表时使用 PRIMARY KEY 约束定义的，并列出构成键的列。

语法

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...,
    PRIMARY KEY (column1, column2)
);

示例：多对多关系

复合键的一个经典用例是链接表（或连接表），它建模多对多关系。例如，让我们模拟学生选课。一个学生可以选多门课程，一门课程可以有多个学生。

-- 首先，创建父表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

-- 现在，创建带有复合键的链接表
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

在 enrollments 表中，student_id 和 course_id 的组合唯一标识每次选课。一个学生只能在特定课程中注册一次。

验证

您可以通过描述表结构来验证复合键。请注意 Key 列中 student_id 和 course_id 旁边的 PRI。

DESCRIBE enrollments;

字段	类型	允许空	键	默认值
student_id	int	NO	PRI	NULL
course_id	int	NO	PRI	NULL
enrollment_date	date	YES		NULL

删除复合键

由于复合键是表的主键，您可以使用 ALTER TABLE ... DROP PRIMARY KEY 语句将其删除。请注意，这会移除键所强制的唯一性和非空约束。

语法

ALTER TABLE table_name DROP PRIMARY KEY;

示例

ALTER TABLE enrollments DROP PRIMARY KEY;

执行此操作后，描述 enrollments 表将显示 student_id 和 course_id 的 Key 列现在为空。

实际应用：在代码中创建复合键

以下是程序化创建带有复合键的表的现代示例。

Node.js (使用 `mysql2/promise`)

const mysql = require('mysql2/promise');

async function createEnrollmentSystem() {
  let connection;
  try {
    connection = await mysql.createConnection({ /* ...数据库配置... */ database: 'school' });
    console.log('Connected to database.');

    const createEnrollmentsTableSQL = `
      CREATE TABLE IF NOT EXISTS enrollments (
        student_id INT NOT NULL,
        course_id INT NOT NULL,
        enrollment_date DATE,
        PRIMARY KEY (student_id, course_id)
      );`;

    await connection.query(createEnrollmentsTableSQL);
    console.log('`enrollments` 表已成功创建，带有复合键。');

  } catch (error) {
    console.error('发生错误:', error);
  } finally {
    if (connection) await connection.end();
  }
}

createEnrollmentSystem();

Python (使用 `mysql-connector-python`)

import mysql.connector
from mysql.connector import errorcode

def create_enrollment_system():
    try:
        with mysql.connector.connect(/* ...数据库配置... */, database='school') as conn:
            with conn.cursor() as cursor:
                sql = ("CREATE TABLE enrollments ("\
                       "  student_id INT NOT NULL,"\
                       "  course_id INT NOT NULL,"\
                       "  enrollment_date DATE,"\
                       "  PRIMARY KEY (student_id, course_id)"\
                       ") ENGINE=InnoDB")
                cursor.execute(sql)
                print("`enrollments` 表已成功创建。")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("表已存在。")
        else:
            print(err.msg)

create_enrollment_system()

PHP (使用 PDO)

<?php
$dsn = 'mysql:host=localhost;dbname=school;charset=utf8mb4';
$user = 'root';
$password = 'password';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

try {
    $pdo = new PDO($dsn, $user, $password, $options);

    $sql = "CREATE TABLE enrollments (
              student_id INT NOT NULL,
              course_id INT NOT NULL,
              enrollment_date DATE,
              PRIMARY KEY (student_id, course_id)
            ) ENGINE=InnoDB;";

    $pdo->exec($sql);
    echo "表 'enrollments' 已成功创建，带有复合键。";

} catch (PDOException $e) {
    echo "错误: " . $e->getMessage();
}
?>