Primary key declaration in MySQL

Introduction

A primary key helps to uniquely identify each row in a table so you can find or update data easily.

When creating a new table to ensure each record is unique.

When you want to quickly find a specific row in a large table.

When you want to prevent duplicate records in a table.

When linking tables together using relationships.

When updating or deleting a specific record safely.

Syntax

MySQL

CREATE TABLE table_name (
  column_name datatype PRIMARY KEY,
  other_column datatype
);

The PRIMARY KEY column must have unique values and cannot be NULL.

You can declare the primary key inline with the column or separately after all columns.

Examples

Primary key declared inline with the StudentID column.

MySQL

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

Primary key declared separately after all columns.

MySQL

CREATE TABLE Employees (
  EmployeeID INT,
  Name VARCHAR(100),
  PRIMARY KEY (EmployeeID)
);

Table with only one column as primary key.

MySQL

CREATE TABLE EmptyTable (
  ID INT PRIMARY KEY
);

Sample Program

This creates a Books table with BookID as the primary key, inserts two books, and then selects all rows.

MySQL

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(255),
  Author VARCHAR(255)
);

INSERT INTO Books (BookID, Title, Author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell');

SELECT * FROM Books;

Important Notes

Primary key columns are automatically indexed for faster searches.

Trying to insert duplicate or NULL values in a primary key column will cause an error.

Use primary keys to link tables with foreign keys for data integrity.

Summary

Primary keys uniquely identify each row in a table.

They prevent duplicate and NULL values in the key column.

You can declare primary keys inline or separately in the table definition.