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.

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.