MySQL PRIMARY KEY

MySQL PRIMARY KEY Constraint

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

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

Explanation:

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

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:

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:

Remove PRIMARY KEY Using ALTER TABLE