BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

UNIQUE Constraint in SQL

Last Updated: September 10, 2022 by Chaitanya Singh | Filed Under: SQL

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a Unique constraint, it means that particular column cannot have duplicate values in a table.

Set UNIQUE Constraint while creating a table

For SQL Server / MS Access / Oracle:

Syntax:

CREATE TABLE <table_name>
(
    <column_name> <data_type> UNIQUE,
    <column_name2> <data_type>,
    ....
    ....
);

Example:

Here we are setting up the UNIQUE Constraint for two columns: STU_NAME & STU_ADDRESS. which means these two columns cannot have duplicate values.

Note: STU_NAME column has two constraints (NOT NULL and UNIQUE both) setup.

CREATE TABLE STUDENTS(
       ROLL_NO   INT           NOT NULL,
       STU_NAME VARCHAR (35)   NOT NULL UNIQUE,
       STU_AGE  INT            NOT NULL,
       STU_ADDRESS  VARCHAR (35) UNIQUE,     
       PRIMARY KEY (ROLL_NO)
);

MySQL:

Syntax:

CREATE TABLE <table_name>
(
    <column_name> <data_type>,
    <column_name2> <data_type>,
    ....
    ....
    UNIQUE(column_name)
);

Example:

Setting up constraint on STU_NAME column.

CREATE TABLE STUDENTS(
       ROLL_NO   INT           NOT NULL,
       STU_NAME VARCHAR (35)   NOT NULL,
       STU_AGE  INT            NOT NULL,
       STU_ADDRESS  VARCHAR (35), 
       UNIQUE(STU_NAME),    
       PRIMARY KEY (ROLL_NO)
);

Naming of UNIQUE Constraint:

MySQL / SQL Server / MS Access / Oracle:

CREATE TABLE STUDENTS(
  ROLL_NO INT NOT NULL,
  STU_NAME VARCHAR (35) NOT NULL,
  STU_AGE INT NOT NULL,
  STU_ADDRESS VARCHAR (35),
  CONSTRAINT stu_Info UNIQUE(STU_NAME, STU_ADDRESS),
  PRIMARY KEY (ROLL_NO)
);

Set UNIQUE Constraint on a already created table

For MySQL / Oracle / SQL Server / MS Access:

For single column and without constraint naming:

Syntax:

ALTER TABLE <table_name>
ADD UNIQUE (<column_name>);

Example:

ALTER TABLE STUDENTS
ADD UNIQUE (STU_NAME);

For multiple columns and with constraint naming:

Syntax:

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> UNIQUE (<column_name1>, <column_name2>,...);

Example:

ALTER TABLE STUDENTS
ADD CONSTRAINT stu_Info UNIQUE (STU_NAME,STU_ADDRESS);

How to drop a UNIQUE Constraint

IN MySQL:

syntax:

ALTER TABLE <table_name>
DROP INDEX <constraint_name>;

Example:

ALTER TABLE STUDENTS
DROP INDEX stu_Info

IN ORACLE / SQL Server / MS Access:

Syntax:

ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;

Example:

ALTER TABLE STUDENTS
DROP CONSTRAINT stu_Info;

Top Related Articles:

  1. NOT NULL Constraint in SQL
  2. Introduction to SQL
  3. SQL – Combining AND, OR and NOT together in where clause
  4. SQL SELECT Database – USE Statement
  5. SQL Tutorial for Beginners: Learn SQL

About the Author

I have 15 years of experience in the IT industry, working with renowned multinational corporations. Additionally, I have dedicated over a decade to teaching, allowing me to refine my skills in delivering information in a simple and easily understandable manner.

– Chaitanya

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2025 BeginnersBook . Privacy Policy . Sitemap