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