beginnersbook.com

  • Home
  • All Tutorials
    • Learn Servlet
    • Learn JSP
    • Learn JSTL
    • Learn C
    • Learn C++
    • Learn MongoDB
    • Learn XML
    • Learn Python
    • Learn Perl
    • Learn Kotlin
  • Core Java
  • OOPs
  • Collections
  • Java I/O
  • JSON
  • DBMS

UNIQUE Constraint in SQL

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;

Enjoyed this post? Try these related posts

  1. DELETE Query in SQL
  2. SQL DELETE Statement
  3. SQL – CREATE DATABASE Statement
  4. SQL SELECT COUNT
  5. NOT NULL Constraint in SQL
  6. SQL – DROP Table Statement to delete the entire table

Leave a Reply Cancel reply

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

SQL Tutorial

  • SQL Introduction
  • SQL Syntax
  • 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

Recently Added..

  • JSON Tutorial
  • Java Regular Expressions Tutorial
  • Java Enum Tutorial
  • Java Annotations Tutorial

Copyright © 2012 – 2021 BeginnersBook . Privacy Policy . Sitemap