UNIQUE Constraint

Ø  The UNIQUE constraint designates a column as a UNIQUE Key.
Ø  A composite UNIQUE key designates a combination of column as the UNIQUE key.
Ø  A composite UNIQUE key is always declared at the table level.
Ø  To satisfy UNIQUE constraints, No two rows in the table can have the same value for the UNIQUE key.
Ø  UNIQUE key made up of a single column can contain NULL values.
Ø  Oracle creates an index implicitly on the UNIQUE key column. 
Restrictions:
Ø  A table or view can have only one UNIQUE key column.
Ø  UNIQUE key cannot be implemented on columns having
Ø  LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, BFILE, REF, TIMESTAMP WITH TIME ZONE.
Ø  A composite UNIQUE key cannot have more than 32 columns.
Ø  Same column or combination of columns cannot be designated as both primary key and UNIQUE key.
Ø  We cannot specify a UNIQUE key when creating a sub table or sub view in an inheritance hierarchy.
Ø  The UNIQUE key can be specified only for the top level (root) table or view.
Syntax:
·         Create Table <Table_Name>
                        (
                                    Column_Name1 <data type> (width) UNIQUE,
                                    Column_Name2 <data type> (width)
                                    CONSTRAINT ConsName UNIQUE,
                                    Column_NameN <data type> (width)
                        );
Illustration: 1
Column Level Syntax:
·         Create Table Promotions
                        (
                                    Promo_ID Number(6)
                                    CONSTRAINT PromoIDUNQ UNIQUE,
                                    PromoName varchar2(20),
                                    PromoCategory varchar2(15),
                                    PromoCost Number(10,2),
                                    PromoBegDate Date,
                                    PromoEndDate Date,
                        );
Illustration:2
Table Level Syntax:
·         Create Table Promotions
                        (
                                    Promo_ID number(6),
                                    PromoName varchar2(20),
                                    PromoCategory varchar2(15),
                                    PromoCost number(10,2),
                                    PromoBegDate Date,
                                    PromoEndDate Date,
                                    CONSTRAINT PromoIDUNQ UNIQUE(Promo_ID)
                        );
Illustration: 3
Composite UNIQUE Constraint Syntax:
·         Create Table Warehouse
                        (
                                    WareHouseID number(6),
                                    WareHouseName varchar2(30),
                                    Area number(4),
                                    Docktype varchar2(50),
                                    Wateraccess varchar2(10),
                                    Railaccess varchar2(10),
                                    Parking varchar2(10),
                                    Vclearance number(4),
                                    CONSTRAINT wareHouseUNQ
                                    UNIQUE(WareHouseID, WareHouseName)
                        );
Illustration: 4
·         Create Table Students
                        (
                                    StudID number(6),
                                    CONSTRAINT StudIDUNQ UNIQUE,
                                    Fname varchar2(30),
                                    DOB Date,
                                    DOJ Date,
                                    EmailID varchar2(50)
                                    CONSTRAINT EmailIDUNQ UNIQUE

                        );