×

Courses

Chapters

>
<

Aptitude

Unique in DBMS

Unique

  • 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.

CREATE

Syntax

  
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 VS Unique 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

→
←