MySQL PRIMARY KEY Indexes

A PRIMARY KEY in MySQL is a unique index that uniquely identifies each row in a table. It ensures that no duplicate values exist for the columns that are part of the key and that the key values are never NULL.

Usage

The PRIMARY KEY is used to enforce the entity integrity of a table by uniquely identifying each record. It is often defined when creating a table and can be composed of one or multiple columns. A table can only have one primary key, which can consist of one or multiple columns.

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

In this syntax, a column is defined as the PRIMARY KEY, ensuring its values are unique and not NULL. Primary keys play a crucial role in database normalization and relational integrity by preventing duplicate records and facilitating efficient data retrieval.

Examples

1. Single Column Primary Key

sql
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100)
);

In this example, user_id is a primary key, automatically incremented for each new record, ensuring unique and non-null values.

2. Composite Primary Key

sql
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

Here, a composite primary key is created using both order_id and product_id, ensuring the combination of these two columns is unique for each row.

3. Adding Primary Key to an Existing Table

sql
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

This example demonstrates adding a primary key to an existing employees table, where employee_id must now be unique across all records.

Tips and Best Practices

• Choose appropriate columns. Select columns with stable and unique values for setting as primary keys, minimizing changes over time to maintain data stability.
• Limit composite keys. Use composite keys only when necessary to maintain simplicity and performance.
• Avoid NULL values. Ensure columns designated as primary keys are defined as NOT NULL.
• Consider AUTO_INCREMENT. Use the AUTO_INCREMENT attribute for numeric primary keys to automatically generate unique values.
• Optimize for performance. Keep the primary key concise to enhance indexing performance and reduce storage space.

Primary keys also establish a relationship with foreign keys in relational databases, enabling the enforcement of referential integrity between tables.