SQL Unique Columns.

Create Unique Column in a table

• PRIMARY KEY constraints gives you normally the unique identifier for each row in a SQL table, and a table can only have one PRIMARY KEY.
• Sometimes we want more unique identifiers for each row in a table, and this is what UNIQUE statement is to be used for.
• You can have as many UNIQUE constraints per table you want. This can also be combined with a PRIMARY KEY constraint.
• Columns included in a UNIQUE constraint can not be specified to be NULL.
• There are mainly two ways to specify a UNIQUE constraints, when you creates a table.
  1. For a single column to be a UNIQUE you can specify UNIQUE at end of the column specification in nearly all RDBMS.
  2. For multiple columns to be UNIQUE you must use a CONSTRAINT statement at end of all columns specification.
Single column to be a UNIQUE constraint example:
MySQL, Oracle, SQL server, PostgreSQL:

CREATE TABLE customer (
  CUSTOMER_ID INT NOT NULL UNIQUE,
  FIRST_NAME VARCHAR(30) NOT NULL,
  LAST_NAME VARCHAR(20) NULL,
  EMAIL VARCHAR(30) NULL
)

In this case it will not be possible to register rows where some have equal value in the column CUSTOMER_ID.

Multiple columns to be a UNIQUE constraint example:
MySQL, Oracle, SQL server, PostgreSQL:

CREATE TABLE customer (
  CUSTOMER_ID INT NOT NULL PRIMARY KEY,
  FIRST_NAME VARCHAR(30) NOT NULL,
  LAST_NAME VARCHAR(40) NOT NULL,
  EMAIL VARCHAR(30) NULL,
  CONSTRAINT pk_CUSTOMER UNIQUE (FIRST_NAME,LAST_NAME)
)

In this case it will not be possible to register rows where some have equal values in the combination of the columns FIRST_NAME and LAST_NAME.

Nor is it possible to register rows where CUSTOMER_ID is duplicated since CUSTOMER_ID is the table's primary key.