MySQL - Composite Key

MySQL: Composite Primary Keys

A Composite Primary Key is a primary key that consists of two or more columns in a table. It is used to uniquely identify each record when no single column, by itself, is sufficient to guarantee uniqueness. The combination of the columns in the composite key must be unique for each row.

Characteristics of a Composite Key

• A table can have only one PRIMARY KEY, which can be either a single column or a composite of multiple columns.
• None of the columns that are part of a composite primary key can contain NULL values.
• The combination of values across all columns in the key must be unique for every row in the table.
• Individually, a column within a composite key can contain duplicate values, as long as the overall combination remains unique.

Creating a Composite Key

A composite key is defined at the time of table creation using the PRIMARY KEY constraint, listing the columns that will form the key.

Syntax

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

Example: A Many-to-Many Relationship

A classic use case for a composite key is a linking table (or junction table) that models a many-to-many relationship. For example, let’s model students enrolling in courses. A student can enroll in many courses, and a course can have many students.

-- First, create the parent tables
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
);

-- Now, create the linking table with a composite key
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)
);

In the enrollments table, the combination of student_id and course_id uniquely identifies each enrollment. A student can only be enrolled in a specific course once.

Verification

You can verify the composite key by describing the table structure. Notice the PRI in the Key column for both student_id and course_id.

DESCRIBE enrollments;

Field	Type	Null	Key	Default	Extra
student_id	int	NO	PRI	NULL
course_id	int	NO	PRI	NULL
enrollment_date	date	YES		NULL

Dropping a Composite Key

Since a composite key is the primary key of the table, you can drop it using the ALTER TABLE ... DROP PRIMARY KEY statement. Be aware that this removes the uniqueness and not-null constraints enforced by the key.

Syntax

ALTER TABLE table_name DROP PRIMARY KEY;

Example

ALTER TABLE enrollments DROP PRIMARY KEY;

After executing this, describing the enrollments table will show that the Key column is now empty for student_id and course_id.

Practical Application: Creating a Composite Key in Code

Here are modern examples of creating a table with a composite key programmatically.

Node.js (with mysql2/promise)

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

async function createEnrollmentSystem() {
  let connection;
  try {
    connection = await mysql.createConnection({ /* ...db config... */ 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` table with composite key created successfully.');

  } catch (error) {
    console.error('An error occurred:', error);
  } finally {
    if (connection) await connection.end();
  }
}

createEnrollmentSystem();

Python (with mysql-connector-python)

import mysql.connector
from mysql.connector import errorcode

def create_enrollment_system():
    try:
        with mysql.connector.connect(/* ...db config... */, 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` table created successfully.")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("Table already exists.")
        else:
            print(err.msg)

create_enrollment_system()

PHP (with 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 "Table 'enrollments' with composite key created successfully.";

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