Primary Key

The primary key constraint uniquely identifies each record in a table. Values in the primary key column must be non-null and cannot contain duplicates. A table can have only one primary key, but it can be defined on a single column or multiple columns.

Single-Column Primary Key

Define the primary key directly when creating the table:

mysql> create table users (
    -> user_id int unsigned primary key,
    -> username varchar(20) not null);

mysql> desc users;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| user_id| int(10) unsigned | NO   | PRI | NULL    |       |
| username varchar(20)      | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+

Attempting to insert duplicate primary key values results in an error:

mysql> insert into users values(1, 'john');
mysql> insert into users values(1, 'jane');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Managing Primary Keys After Table Creation

Add a primary key to an existing table:

alter table table_name add primary key(column_name);

Remove a primary key from a table:

alter table table_name drop primary key;

Composite Primary Key

A composite primary key uses multiple columns to ensure uniqueness. The combination of values across these columns must be unique:

mysql> create table enrollments (
    -> student_id int unsigned,
    -> class_code char(10),
    -> grade tinyint unsigned default 0,
    -> primary key(student_id, class_code)
    -> );

mysql> desc enrollments;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| student_id| int(10) unsigned    | NO   | PRI | 0       |       |
| class_code| char(10)            | NO   | PRI |         |       |
| grade     | tinyint(3) unsigned | YES  |     | 0       |       |
+-----------+---------------------+------+-----+---------+-------+

The same student cannot enroll in the same class twice:

mysql> insert into enrollments (student_id, class_code) values(5, 'CS101');
mysql> insert into enrollments (student_id, class_code) values(5, 'CS101');
ERROR 1062 (23000): Duplicate entry '5-CS101' for key 'PRIMARY'

---

Auto Increment

The auto_increment attribute automatically generates sequential values when no value is provided for that column. The system increments from the current maximum value.

Key Characteristics

• The column must be indexed (have a key assigned)
• Only numeric types support auto increment
• A table can have only one auto increment column
• Typically paired with primary key to serve as a surrogate key

Example Usage

mysql> create table products (
    -> product_id int unsigned primary key auto_increment,
    -> product_name varchar(20) not null
    -> );

mysql> insert into products (product_name) values('Widget');
mysql> insert into products (product_name) values('Gadget');

mysql> select * from products;
+----+--------------+
| product_id | product_name |
+----+--------------+
| 1          | Widget       |
| 2          | Gadget       |
+----+--------------+

Retrieve the last auto-generated value:

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2                |
+------------------+

---

What Are Indexes?

An index is a data structure that improves query speed by maintaining sorted values for one or more columns. Think of it like a book's index—instead of scanning every page, you find your target through organized references. Database indexes work similarly, storing pointers to data pages and enabling rapid lookups.

---

Unique Key

While a table can only have one primary key, many columns may require unique values. The unique key constraint enforces uniqueness across non-primary columns while allowing NULL values. Multpile NULL values are permittde since NULL comparisons always evaluate to false.

Practical Difference Between Primary and Unique Keys

• Primary keys emphasize record identification
• Unique keys ensure business-level data uniqueness

Consider an employee system with both identification number and employee ID. The identification number might serve as the primary key since it's guaranteed never to change. The employee ID, however, must also be unique across the organization—a perfect use case for a unique key.

It is advisable to design primary keys using values unrelated to business logic, allowing business rules to change without altering the primary key structure.

Example

mysql> create table employees (
    -> emp_number char(10) unique,
    -> emp_name varchar(20)
    -> );

mysql> insert into employees (emp_number, emp_name) values('E001', 'Alice');
mysql> insert into employees (emp_number, emp_name) values('E001', 'Bob');
ERROR 1062 (23000): Duplicate entry 'E001' for key 'emp_number'

mysql> insert into employees (emp_number, emp_name) values(null, 'Charlie');

mysql> select * from employees;
+-----------+----------+
| emp_number| emp_name |
+-----------+----------+
| E001      | Alice    |
| NULL      | Charlie  |
+-----------+----------+

---

Foreign Key

The Problem Without Foreign Keys

Imagine a students table with a class_id referencing a classes table. Without constraints:

• A student record could reference a non-existent class
• A class could be deleted even if students are assigned to it

The class_id column creates an association but lacks enforcement.

Definition

A foreign key establishes referential integrity between two tables:

• Defined on the child (referencing) table
• References a primary key or unique key on the parent (referenced) table
• Values must either exist in the parent table or be NULL

Syntax

foreign key (column_name) references parent_table(parent_column)

Practical Example

Consider separating class information into its own table to avoid data redundancy:

Create the parent table first:

create table classes (
    class_id int primary key,
    class_name varchar(30) not null
);

Create the child table with the foreign key:

create table students (
    student_id int primary key,
    student_name varchar(30) not null,
    class_id int,
    foreign key (class_id) references classes(class_id)
);

Insert valid data:

mysql> insert into classes values(10, 'Grade 10A'),(20, 'Grade 10B');

mysql> insert into students values(100, 'Tom', 10),(101, 'Jerry', 20);

Violations are rejected:

mysql> insert into students values(102, 'NewStudent', 30);
ERROR 1452 (23000): Cannot add or update a child row: 
foreign key constraint fails

NULL is permitted for unassigned students:

mysql> insert into students values(103, 'Unassigned', null);

Why Use Foreign Keys?

Without foreign key constraints, related tables operate independently despite their logical connection. This creates risk of data inconsistency—theoretically, you could insert a student referencing a class that doesn't exist.

Foreign keys shift this responsibility to MySQL. By declaring the relationship, the database automatically validates data integrity, rejecting any insert or update that violates the established referential rules.

---

Complete Example: E-Commerce Database

A shop system with products, customers, and orders:

-- Create database
create database if not exists shop_db
default character set utf8;

use shop_db;

-- Products table
create table if not exists products (
    product_id int primary key auto_increment,
    product_name varchar(32) not null,
    price int not null default 0 comment 'Price in cents',
    category varchar(12),
    supplier varchar(64) not null
);

-- Customers table
create table if not exists customers (
    customer_id int primary key auto_increment,
    full_name varchar(32) not null,
    address varchar(256),
    email varchar(64) unique,
    gender enum('Male','Female') not null,
    id_number char(18) unique
);

-- Orders table (references both products and customers)
create table if not exists orders (
    order_id int primary key auto_increment,
    customer_id int,
    product_id int,
    quantity int default 0,
    foreign key (customer_id) references customers(customer_id),
    foreign key (product_id) references products(product_id)
);

Requirements implemented:

• Each table has an auto-incrementing primary key
• Customer names are required
• Email addresses are unique
• Gender is restricted to Male/Female
• Orders maintain referential integrity with customers and products