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.
- For a single column to be a UNIQUE you can specify UNIQUE at end of the column specification in nearly all RDBMS.
- For multiple columns to be UNIQUE you must use a CONSTRAINT statement at end of all columns specification.
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.
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.