A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique-that is, no two rows of a table have duplicate values in a specified column or set of columns.
Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences:
• There can be only one Primary key per table. Whereas, you can have as many Unique Keys per table as you want.
• Primary key does not accept NULL values whereas; unique key columns can be left blank.
It depends on situations; first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, since a table can have only one primary key, you can define Unique Key constraint on these columns.
Second situation is when a column should not contain any duplicate value but it should also be left blank.
Syntax: At column Level:
(Column_name data type [Constraint <name>] UNIQUE,
Syntax: At table Level:
[Constraint <name>] UNIQUE (<column_name>);
Query: The following SQL enforces the "P_Id" column to take unique values.
SQL> CREATE TABLE Persons(p_Id number (6) UNIQUE,LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15))
Table created.
Query: Table level
SQL> CREATE TABLE Persons(P_Id number (6),LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15)Email varchar2(20), constraint UN_persons UNIQUE (email));
Table created.
Query: To define a UNIQUE KEY constraint on an existing table give the following command.
SQL> Alter table v add constraint UN_v unique(roll); OR
SQL> Alter table v add unique (roll);
Dinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.
Related Articles
Basic Courses
Advance Courses