MYSQL Tutorial

MySQL PRIMARY KEY

A PRIMARY KEY uniquely identifies each row in a table. It enforces both NOT NULL and UNIQUE constraints on the column(s) it’s defined on.

Why Use a Primary Key?

• Ensures each record can be uniquely identified.
• Improves query performance via indexed lookups.
• Supports relational integrity when used as a foreign key in other tables.

Defining a Primary Key

You can declare a primary key when creating a table or add it later:

-- Inline definition during CREATE
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

-- Separate constraint declaration
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  PRIMARY KEY (order_id)
);
  

Composite Primary Key

A composite key uses multiple columns to ensure uniqueness:

CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id)
);
  

Altering an Existing Table

Add or drop a primary key on an existing table:

-- Add primary key
ALTER TABLE products
ADD PRIMARY KEY (sku);

-- Drop primary key
ALTER TABLE products
DROP PRIMARY KEY;
  

Best Practices

• Use an AUTO_INCREMENT integer for simple, single-column keys.
• Ensure composite keys are only used when necessary for natural uniqueness.
• Avoid changing primary key values once data is in production.
• Always define a primary key on every table to maintain data integrity.