MySQL PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each row in a table. Primary-key columns are unique and cannot contain NULL. A table can have only one primary key, although that key can contain more than one column.

CREATE TABLE student (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(20) NOT NULL,
    mark TINYINT UNSIGNED NOT NULL DEFAULT 0,
    gender VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

Create a Primary Key with the Table

Defining the key at table creation time is normally the clearest approach:

CREATE TABLE student (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Add a Primary Key to an Existing Table

ALTER TABLE student
ADD PRIMARY KEY (id);

The existing column values must satisfy the key rules: no duplicates and no NULL values.

Composite Primary Key

A primary key can use more than one column when the combination uniquely identifies a row:

ALTER TABLE enrollment
ADD PRIMARY KEY (student_id, course_id);

Drop a Primary Key

ALTER TABLE student
DROP PRIMARY KEY;

Primary Key vs UNIQUE

For InnoDB, secondary-index entries also contain the primary-key columns, so a short primary key is usually preferable.

Inspect Keys and Constraints

SHOW CREATE TABLE student;

SHOW INDEX FROM student;

You can also query INFORMATION_SCHEMA when you need metadata across many tables.

Duplicate Primary-Key Values and INSERT IGNORE

A normal insert that violates a primary key produces an error:

INSERT INTO student (id, name, class, mark, gender) VALUES
(10, 'Test name', 'Four', 55, 'male'),
(36, 'Test name 2', 'Four', 57, 'male');

MySQL supports INSERT IGNORE, which can convert certain errors, including duplicate-key violations, into warnings so processing can continue:

INSERT IGNORE INTO student (id, name, class, mark, gender) VALUES
(10, 'Test name', 'Four', 55, 'male'),
(36, 'Test name 2', 'Four', 57, 'male');

Example: Student ID and Phone Number

A student ID is a natural primary key candidate because every row requires one unique identifier. A phone-number column may instead use a UNIQUE constraint while remaining nullable when a student has not supplied a number.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: