MySQL - Primary Key

MySQL - Primary Key Explained

A PRIMARY KEY is a fundamental constraint in database design. It uniquely identifies each record in a table, ensuring data integrity and serving as a stable reference point for relationships with other tables.

Core Properties of a Primary Key

• Uniqueness: Every value in the primary key column (or combination of columns) must be unique. No two rows can have the same primary key value.
• Non-Nullability: A primary key column cannot contain NULL values. Every row must have a primary key value.
• A table can have only one primary key, but this key can consist of one or more columns (known as a composite primary key).

Choosing a Primary Key: Natural vs. Surrogate

When choosing a primary key, you have two main options:

• Natural Key: A key that is formed of attributes that already exist in the real world. For example, a user’s email or a country’s iso_code. They are meaningful but can sometimes change, be long, or not be guaranteed unique.
• Surrogate Key: An artificial key that has no business meaning, created solely to be the primary key. The most common type is an auto-incrementing integer (AUTO_INCREMENT). They are stable, efficient, and guaranteed to be unique within the table.

Best Practice: For most applications, using a surrogate key (INT or BIGINT with AUTO_INCREMENT) is the recommended approach. It simplifies relationships and is unaffected by changes in business data.

Defining a Primary Key

1. During Table Creation

The most common way to define a primary key is within the CREATE TABLE statement.

Example (Single-Column Surrogate Key):

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Example (Composite Primary Key): A composite key is useful when a combination of columns is needed to uniquely identify a row.

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price_per_unit DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id) -- Composite Key
    -- Assuming FOREIGN KEYs would be added to link to orders and products tables
);

2. Adding to an Existing Table

You can add a primary key to an existing table using the ALTER TABLE statement. This is only possible if the table does not already have a primary key and the column(s) you select are NOT NULL and contain unique values.

Example:

-- First, create a table without a primary key
CREATE TABLE employees (
    employee_code VARCHAR(10) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

-- Add some data
INSERT INTO employees (employee_code, first_name, last_name) VALUES
('EMP001', 'Jane', 'Doe'),
('EMP002', 'John', 'Smith');

-- Now, add the primary key constraint
ALTER TABLE employees
ADD PRIMARY KEY (employee_code);

Verification

You can verify the key was created by using DESCRIBE or SHOW CREATE TABLE.

DESCRIBE employees;

Field	Type	Null	Key	Default	Extra
employee_code	varchar(10)	NO	PRI	NULL
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL

Attempting to insert a duplicate employee_code will now result in an error.

INSERT INTO employees (employee_code, first_name, last_name) VALUES ('EMP001', 'Jim', 'Beam');
-- ERROR 1062 (23000): Duplicate entry 'EMP001' for key 'employees.PRIMARY'

Dropping a Primary Key

You can remove a primary key constraint, but be very cautious. This is a significant structural change that can break foreign key relationships and lead to data integrity problems.

Syntax

ALTER TABLE table_name DROP PRIMARY KEY;

Example

ALTER TABLE employees DROP PRIMARY KEY;

Creating a Primary Key Using a Client Program

Here are examples of creating a table with a primary key using various programming languages, following modern best practices.

Python
NodeJS
Java
PHP



import mysql.connector
from mysql.connector import errorcode

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

create_table_query = """
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;
"""

try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("DROP TABLE IF EXISTS products") # Clean up before running
            cursor.execute(create_table_query)
            print("Table 'products' created successfully with a primary key.")
except mysql.connector.Error as err:
    print(f"Failed to create table: {err}")



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

const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
});

const createTableWithPK = async () => {
    const query = `
    CREATE TABLE articles (
        article_id CHAR(36) PRIMARY KEY, -- Using UUID for primary key
        title VARCHAR(255) NOT NULL,
        author_id INT NOT NULL,
        published_date DATE
    );
    `;

    try {
        await pool.query('DROP TABLE IF EXISTS articles'); // For idempotency
        await pool.query(query);
        console.log('Table \"articles\" created successfully with a UUID primary key.');
    } catch (error) {
        console.error('Failed to create table:', error);
    } finally {
        await pool.end();
    }
};

createTableWithPK();



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreatePrimaryKey {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String query = """
            CREATE TABLE categories (
                category_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL
            )
        """;

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            stmt.execute("DROP TABLE IF EXISTS categories"); // Clean up for reruns
            stmt.execute(query);
            System.out.println("Table 'categories' created successfully with a primary key.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



<?php
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

$query = "
CREATE TABLE logs (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(10) NOT NULL,
    message TEXT,
    log_time DATETIME NOT NULL
);
";

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

    $pdo->exec('DROP TABLE IF EXISTS logs'); // Ensure clean slate
    $pdo->exec($query);

    echo "Table 'logs' created successfully with a primary key.\n";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>