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
%%sql
use retail_db;
drop table if exists emp_<<your lab username>>;
drop table if exists deptt_<<your lab username>>;
create table deptt_<<your lab username>> (
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
%%sql
use retail_db;
drop table if exists emp_<<your lab username>>;
drop table if exists deptt_<<your lab username>>;
create table deptt_<<your lab username>> (
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
%%sql
use retail_db;
drop table if exists emp_<<your lab username>>;
drop table if exists deptt_<<your lab username>>;
create table deptt_<<your lab username>> (
depid int not null,
depname varchar(100) not null,
depcity varchar(100) not null,
depstreet varchar(100) not null,
depopendate date
);
alter table deptt_<<your lab username>> add primary key (depid);
alter table deptt_<<your lab username>> drop primary key;
alter table deptt_<<your lab username>> add primary key pk_deptt (depid);
alter table deptt_<<your lab username>> drop primary key;
- View the table definition using below commands:
describe deptt_<<your lab username>>;
OR
show create table deptt_<<your lab username>>;
Loading comments...