Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

Unique in DBMS

Unique in DBMS 

 

In this article, we will learn about Unique in DBMS.

  • Sometimes we need to maintain only unique data   in the column of a database table, this is possible by using a unique constraint 
  • Unique constraint ensures that all values in a column are unique 
  • If we unique constraint is applied to a column then all the values present the column must be unique i.e if you try to enter a duplicate data then will be shown an error
  • A unique key can be applied to any number of columns in a table.
Unique in DBMS

Syntax to create a unique constraint 


CREATE
TABLE table_name(
    column1 datatype(size) UNIQUE,    column2 datatype(size) , column3 datatype(size) , ); 

 

Example 

CREATE TABLE Persons (
    ID int UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
); 

In the above example, as we have used unique constraint on ID column we are not supposed to enter the data that is already present, simply no two ID values are same.

 

Combination of not null and unique 

  • You can use multiple constraints on a single column 
  • Whenever you apply both not null and unique constraints to a column it restricts to have only unique data values and also show it prevents null values  at the same time.

Example 

    ID int NOT NULL UNIQUE 

 

Primary key vs Unique key 

  • Both primary key and unique key will not allow duplicate values but the difference is that, the primary key will not accept null values where as unique key allows null values on the column 
  • Simply only one unique + not null= primary key 

Primary key 

  • Identifies  a record uniquely in a table 
  • It will not allow null values 
  • A clustered index is created
  • Only one primary key is allowed in a table

Unique key 

  • It helps to maintain unique data in the required columns of the table 
  • It will allow null values 
  • Non clustered indexes are created 
  • A unique key can be applied to any number of columns in the table