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)
CREATE TABLE engineer (eng_name char(4), eng_id char(4) CONSTRAINT uniEng_id UNIQUE)
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)
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