Learn about the MySQL Primary Key constraint used to uniquely identify each record in a table. Explore syntax, examples, and best practices for defining and managing primary keys.

A PRIMARY KEY in MySQL is a constraint that uniquely identifies each record in a table. It ensures that no two rows have the same value in the specified column(s) and prevents NULL values.

Key Characteristics:

Uniqueness: Each value must be unique.
Not Null: Cannot contain NULL values.
Single or Composite: Can be applied to one or multiple columns.
One per Table: Each table can have only one primary key.

Syntax for PRIMARY KEY During Table Creation

For Single Column Primary Key:

CREATE TABLE table_name (
  column1 data_type PRIMARY KEY,
  column2 data_type,
  column3 data_type
);

For Composite Primary Key (Multiple Columns):

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type,
  PRIMARY KEY (column1, column2)
);

PRIMARY KEY on CREATE TABLE with Single Column Primary Key

Example

CREATE TABLE students (

student_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE,

age INT

);

Explanation:

student_id is the Primary Key (Unique and Not Null).
name is Not Null, meaning it cannot be empty.
email is Unique to avoid duplicates.

Create Table with Composite Primary Key

Example

CREATE TABLE enrollments (

student_id INT,

course_id INT,

enrollment_date DATE,

CONSTRAINT pk_student PRIMARY KEY (student_id, course_id)

);

Explanation:

student_id and course_id together form a Composite Primary Key.
A student cannot enroll in the same course multiple times.

Note

In the example above there is only ONE PRIMARY KEY (pk_student). However, the VALUE of the primary key is made up of TWO COLUMNS (student_id + course_id).

Add PRIMARY KEY to Existing Table Using ALTER TABLE

To create a PRIMARY KEY constraint on the "student_id" column when the table is already created, use the following SQL:

Example

ALTER TABLE students

ADD CONSTRAINT pk_student PRIMARY KEY (student_id);

Explanation:

pk_student is the name of the primary key constraint.

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

Example

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Remove PRIMARY KEY Using ALTER TABLE

Example

ALTER TABLE students

DROP PRIMARY KEY;

Note

If the primary key is a composite key, you will need to remove the entire key using this method.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.