1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle UNIQUE CONSTRAINT

Discussion in 'SQL PL/SQL' started by SBH, Nov 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    In Oracle, entity integrity of the data is maintained by enforcing UNIQUE and PRIMARY KEY constraints. The article explains the important aspects of Unique constraints in Oracle.

    2. Introduction

    Unique constraint implements entity integrity of data in the database. The column(s) declared Unique does not allow duplication of values but can contain NULL values. Unique constraint can be enforced on one or multiple columns. Like all other constraints, a column can be declared unique at Column level (inline declaration), which allows only one column to come under unique effect. Other way is to declare at Table level (out of line declaration) which allows multiple columns to participate in the constraint declaration.
    [​IMG]

    3. Syntax

    Column Level:
    Code (Text):
    COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]
    Table Level:
    Code (Text):
    CONSTRAINT [constraint name] UNIQUE (column name)
    4. Example

    4.1. Below command shows the constraint declaration at Column level

    Code (SQL):
    CREATE TABLE TEST
    ( ... ,
      NAME VARCHAR2(20)
              CONSTRAINT TEST_NAME_UK UNIQUE,
      ... );
    4.2. In case of composite unique key, it must be defined at table level as below.

    Code (SQL):
    CREATE TABLE TEST
    ( ... ,
      NAME VARCHAR2(20),
      STD VARCHAR2(20) ,
          CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
     );
    5. Unique Index with Unique constraint

    Oracle server implicitly creates a unique constraint on the column(s) participating under Unique constraint. It can be queried under INDEX_NAME column of USER_CONSTRAINTS columns.

    6. Notes

    1. Unique constraint can be enforced only on the columns of native data types. Columns of type LOB, LONG, Collections, Objects, TIMESTAMP WITH TIME ZONE cannot participate in the constraint declaration.
    2. A column cannot be part of both primary key and unique constraint.
    3. Composite Unique constraint can contain maximum of 32 columns.
     

    Attached Files: