Constraints : Primary Key


Primary Key
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.
DetailPrimary KeyUnique Key
Null valueNot allowedAllowed
Number of ConstrintsOne onlyOne or more
IndexClustered IdexNon-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
Alter Table How to delete Records in different tabels

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer