Primary Key constraint of MySQL table

One table can have one Primary Key constrain and this is used to uniquely identify the row. Primary Key can’t have Null data.

Creating and deleting Primary Key constraint of Table and difference between Unique Key in MySQL

Creating a Primary Key

For a New table

CREATE TABLE `student` (
 `id` int(2) NOT NULL,
 `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
 `class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
 `mark` int(3) NOT NULL DEFAULT '0',
 `gender` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Adding Primary Key to an existing table for single column.

ALTER TABLE `student` ADD PRIMARY KEY ( `id` ) ;

Using two columns

ALTER TABLE `student` ADD PRIMARY KEY ( `id` , `name` ) ;

Deleting Primary Key

ALTER TABLE student DROP PRIMARY KEY

Difference between Primary Key and Unique constraint

Null value is not allowed in Primary Key, however Unique constrains allows Null value.
There can be only one Primary key, we can have multiple columns with Unique constrains.

Detail	Primary Key	Unique Key
Null value	Not allowed	Allowed
Number of Constrints	One only	One or more
Index	Clustered Idex	Non-Clustered Index

Displaying all constrains of a table

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'student'

Using SHOW

SHOW CREATE TABLE student

IGNORE

Here the first record with id=10 is violating the duplicate entry for Primary Key on ID column. Due to this error no record is added.

INSERT INTO `my_tutorial`.`student` 
(`id` ,`name` ,`class` ,`mark` ,`gender`)

VALUES ('10', 'test name', 'Four', '55', 'male'), 
('36', 'test name 2', 'Four', '57', 'male')

We can use IGNORE to ask MySQL to ignore the error for the line id=10 and continue adding the next line. Here one record with id=36 will be added.

INSERT  IGNORE INTO `my_tutorial`.`student` 
(`id` ,`name` ,`class` ,`mark` ,`gender`)

VALUES ('10', 'test name', 'Four', '55', 'male'), 
('36', 'test name 2', 'Four', '57', 'male')

The error we got depends on setting of STRICT mode.

Example: Primary Key & Unique Key constraint

In our student table we will have student roll number (or student id) , similarly in one more column we are storing Mobile phone number of each student.

Example Primary Key

Here we have to use student id as Primary key but phone column can have Unique constraint. Some student may not have submitted phone number so we can have Null data for that row phone column, but we can’t keep student id as null.

There can’t be any student without student id or student roll number so Null data is not allowed in student id column.
The phone numbers of each student has to be unique. Similarly student id ( Primary Key ) is also have unique data.
SQL References Collecting Unique ID after inserting data AUTO_INCREMENT
Alter Table How to delete Records in different tabels

Subscribe to our YouTube Channel here

Constraints : Primary Key