SQL UNIQUE Constraint

The UNIQUE constraint is used to uniquely identify each row in a database table. It is some similar to Primary key, but has some difference:

  • Primary Key is automatically Unique Constraint
  • One table should have ONE primary key, but could have mutiple Unique Constraint

To create a table with UNIQUE Constraint:

SQL Server

CREATE TABLE Users
(
    UserID int NOT NULL UNIQUE ,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL 
)

My SQL

CREATE TABLE Users
(
    UserID int NOT NULL,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL,
    UNIQUE (UserID )
)

You can also set Unique Constraint by combining multiple columns:
My SQL / SQL Server / Oracle

CREATE TABLE Users
(
    UserID int NOT NULL,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL,
    CONSTRAINT uc_UserFullName UNIQUE (FirstName, LastName )
)

To set UNIQUE Constraint by using ALTER TABLE:

SQL Server / MySQL / Oracle / MS Access:

    ALTER TABLE Users
    ADD UNIQUE (UserID)

A UNIQUE Constraint can be a combine of multiple columns:
SQL Server / MySQL / Oracle / MS Access:

    ALTER TABLE Users
    ADD CONSTRAINT uc_UserFullName UNIQUE (FirstName, LastName )

To drop a UNIQUE Constraint:

SQL Server:

    ALTER TABLE Users
    DROP CONSTRAINT uc_UserFullName

My SQL:

    ALTER TABLE Users
    DROP INDEX uc_UserFullName

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>