Ø 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
);