MySQL - Unique Key

MySQL - The UNIQUE Constraint

A UNIQUE constraint in MySQL is a rule that ensures all values in a column or a set of columns are distinct from one another. It’s a fundamental tool for enforcing data integrity, preventing duplicate records, and ensuring that specific fields can be used to uniquely identify a row in a table.

When you apply a UNIQUE constraint, MySQL automatically creates a UNIQUE index on the specified column(s). This not only enforces uniqueness but also helps to speed up queries that filter on these columns.

UNIQUE vs. PRIMARY KEY: The Key Differences

While both constraints enforce uniqueness, they have important distinctions:

Feature	PRIMARY KEY	UNIQUE Constraint
NULL Values	Does NOT allow NULL values. A primary key column must always have a value.	Allows multiple NULL values. NULL is considered non-equal to other NULLs.
Number per Table	A table can have only ONE PRIMARY KEY.	A table can have MULTIPLE UNIQUE constraints.
Clustered Index	In InnoDB, the PRIMARY KEY is also the clustered index, determining the physical storage order of rows.	Creates a secondary, non-clustered index.
Main Purpose	To be the primary, definitive identifier for a row in the table.	To ensure uniqueness for a secondary or alternative key (e.g., email address, product SKU).

Creating a UNIQUE Constraint on a New Table

You can define a UNIQUE constraint when you create a table using the CREATE TABLE statement.

Syntax

-- Method 1: Inline definition
CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Method 2: Table-level constraint definition (better for named constraints)
CREATE TABLE Products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(20) NOT NULL,
    product_name VARCHAR(255),
    CONSTRAINT uc_product_sku UNIQUE (sku)
);

Naming your constraint (like uc_product_sku) is a best practice, as it makes it much easier to identify and drop later.

Handling Violations of the UNIQUE Constraint

If you attempt to INSERT or UPDATE a row with a value that already exists in a UNIQUE column, MySQL will reject the operation and return an error.

-- Let's insert a user
INSERT INTO Users (username, email) VALUES ('john.doe', 'john.doe@example.com');

-- Now, let's try to insert another user with the same email
INSERT INTO Users (username, email) VALUES ('johndoe2', 'john.doe@example.com');

The second INSERT statement will fail with an error message like:

ERROR 1062 (23000): Duplicate entry 'john.doe@example.com' for key 'users.email'

Your application code must be prepared to handle this error gracefully.

Adding a UNIQUE Constraint to an Existing Table

You can add a UNIQUE constraint to a table that already exists using the ALTER TABLE statement. This will fail if the column already contains duplicate values.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Example

Imagine we have an Employees table and want to ensure employee_id is unique.

ALTER TABLE Employees
ADD CONSTRAINT uc_employee_id UNIQUE (employee_id);

Creating Composite UNIQUE Constraints

A UNIQUE constraint can also be applied to multiple columns. This ensures that the combination of values in those columns is unique, though individual values can be repeated.

A common example is preventing a student from enrolling in the same course more than once.

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade CHAR(1),
    CONSTRAINT uc_student_course UNIQUE (student_id, course_id)
);

In this table, student_id 1 can enroll in course_id 101, and student_id 2 can also enroll in course_id 101. However, student_id 1 cannot enroll in course_id 101 a second time.

Dropping a UNIQUE Constraint

To remove a UNIQUE constraint, you use the ALTER TABLE ... DROP CONSTRAINT statement. This is why naming your constraints is so helpful.

ALTER TABLE Products DROP CONSTRAINT uc_product_sku;

If you didn’t name the constraint, MySQL assigned a name automatically (usually the same as the column name). You can find the name using SHOW CREATE TABLE Products; and then drop it using that name.

Creating a UNIQUE Constraint Using Client Programs

Here are examples of how to create a table with a UNIQUE constraint and handle potential duplicate entry errors in various languages.

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


This Node.js script attempts to insert a user and catches the specific error for duplicate entries.

```javascript
// Requires: npm install mysql2
const mysql = require('mysql2/promise');

async function addUser(username, email) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost', user: 'root', password: 'your_password', database: 'your_database'
    });

    const sql = 'INSERT INTO Users (username, email) VALUES (?, ?)';
    await connection.execute(sql, [username, email]);
    console.log(`User '${username}' added successfully.`);

  } catch (error) {
    // MySQL error code for duplicate entry is 1062
    if (error.code === 'ER_DUP_ENTRY') {
        console.error(`Error: A user with that username or email already exists.`);
    } else {
        console.error(`An unexpected error occurred: ${error.message}`);
    }
  } finally {
    if (connection) await connection.end();
  }
}

// First call will succeed, second will fail with the custom error message
addUser('jane.doe', 'jane.doe@example.com');
// addUser('jane.doe', 'another.email@example.com'); // This would also fail

Python’s mysql.connector has specific error codes you can check for.

# Requires: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def add_user(username, email):
    try:
        with mysql.connector.connect(user='root', password='your_password', database='your_database') as conn:
            with conn.cursor() as cursor:
                sql = 'INSERT INTO Users (username, email) VALUES (%s, %s)'
                cursor.execute(sql, (username, email))
                conn.commit()
                print(f"User '{username}' added successfully.")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_DUP_ENTRY:
            print(f"Error: A user with username '{username}' or email '{email}' already exists.")
        else:
            print(f"Database error: {err}")

# Example usage
add_user('peter.jones', 'peter.jones@example.com')
add_user('another.user', 'peter.jones@example.com') # This will trigger the error

In Java, you catch SQLException and check the vendor-specific error code.

import java.sql.*;

public class UniqueConstraintExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "root";
        String pass = "your_password";
        String sql = "INSERT INTO Users (username, email) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "susan.adams");
            pstmt.setString(2, "s.adams@example.com");
            pstmt.executeUpdate();
            System.out.println("User 'susan.adams' added successfully.");

        } catch (SQLException e) {
            // MySQL's error code for duplicate entry is 1062
            if (e.getErrorCode() == 1062) {
                System.err.println("Error: A user with that username or email already exists.");
            } else {
                e.printStackTrace();
            }
        }
    }
}

In PHP, you can check the errno property of the mysqli object after a failed query.

<?php
$mysqli = new mysqli('localhost', 'root', 'your_password', 'your_database');
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$username = 'test_user';
$email = 'test@example.com';

$sql = "INSERT INTO Users (username, email) VALUES (?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ss", $username, $email);

if ($stmt->execute()) {
    echo "User '$username' added successfully.";
} else {
    // MySQL's error number for duplicate entry is 1062
    if ($mysqli->errno == 1062) {
        echo "Error: Username or email already exists.";
    } else {
        echo "An error occurred: " . $mysqli->error;
    }
}

$stmt->close();
$mysqli->close();
?>