DATABASE & SQL/PLSQL

To enforce uniqueness on non-primary columns used the UNIQUE constraints. A primary key constraints column automatically includes a restriction for uniqueness. The unique constraint is similar to the primary key constraint except that it allows NULL values, but there can be only one row in the table with a NULL value. Multiple Unique constraints can be created on a table.

CREATE TABLE  table_name
(coloumn_name  data_type  CONSTRAINT  constraint_name  UNIQUE)

Example

CREATE TABLE  engineer
(eng_name  char(4),  eng_id  char(4) CONSTRAINT  uniEng_id  UNIQUE)

output

The above command creates a UNIQUE constraint uniEng_id on the attribute eng_id in the engineer table. The constraints would not allow the duplicate value to be inserted in the uniEng_id attribute. However, there could be one row in the uniEng_id attribute that contains NULL.

We create a UNIQUE constraints after the table has been created by altering the table

ALTER TABLE  engineer
ADD CONSTRAINT  UNeng_SocialId  UNIQUE  (eng_SocialId)

output

The above command creates a UNIQUE constraint UNeng_SocialId on the attribute eng_SocialId in the engineer table.

The Rules regarding to the UNIQUE constraints are:

• It can be created at the column level as well as table level
• It does not allow two rows to have the same non-null value in a table
• Multiple UNIQUE constraints can be placed on a table