Constraint Keys on Tables - Primary and Unique Key

Primary Key and Unique Key

Tables represent some real-world data and in most of the cases, one row in a table represents one real-world entity. Hence, exact data should not be repeating across the columns.
For example, in a table storing students information, one roll number should not occur more than once but names can repeat because more than one students can have same name.
To enforce such a requirement, the database provides a constraint mechanism called Primary Key which is created at the table level comprising of the columns which can not repeat in the table.
In the current example, roll number would be the column of the Primary Key.
Unique Key is another type of table constraint to enforce the uniqueness of the data in a table with certain differences when compared to Primary Key.

Primary Key vs Unique Key

Primary Key columns cannot be the NULLABLE columns and cannot have null values while Unique Key columns can have.
A table can have only one Primary Key but can have multiple Unique Keys.

Auto Generation of Primary/Unique Keys

Primary/Unique Key values can have predefined values or can be generated using AUTO_INCREMENT option for a column in real time as data in inserted in tables.
AUTO_INCREMENT option automatically generates next number and assigns to Primary/Unique key column.
There can be only one column with an AUTO_INCREMENT option in a given table.
If AUTO_INCREMENT is used, Primary/Unique Key must be defined as part of table creation itself using the AUTO_INCREMENT column.
If AUTO_INCREMENT is not used, Primary/Unique Key may be defined as part of table creation or may be added later.
Column with such an option must be added as Primary Key while creating the table.
You can get last inserted id value using below command and we will see examples on this later.
SELECT LAST_INSERT_ID();

Let's see all above in action.
With AUTO_INCREMENT and Primary key

use retail_db
drop table deptt;
create table deptt (
    depid int not null auto_increment,
    depname varchar(100) not null,
    depcity varchar(100) not null,
    depstreet varchar(100) not null,
    depopendate date,
    primary key ( depid )
);

Without AUTO_INCREMENT and with Primary key

use retail_db
drop table deptt;
create table deptt (
    depid int not null,
    depname varchar(100) not null,
    depcity varchar(100) not null,
    depstreet varchar(100) not null,
    depopendate date,
    primary key ( depid )
);

Without AUTO_INCREMENT and with the Primary key added later

use retail_db
drop table deptt;
create table deptt (
    depid int not null,
    depname varchar(100) not null,
    depcity varchar(100) not null,
    depstreet varchar(100) not null,
    depopendate date
);
--system named primary key
alter table deptt add primary key (depid);
alter table deptt drop primary key;
--user named primary key
alter table deptt add primary key pk_deptt (depid);
alter table deptt drop primary key;

View the table definition using below commands:
describe deptt;
OR
show create table deptt;

Previous Index Next

SQL Tutorial

Constraint Keys on Tables - Primary and Unique Key

XP

Loading comments...