UNIQUE

What is the UNIQUE constraint?

• The UNIQUE constraint ensures that all values in a column (or group of columns) are different.
• It prevents two records from having identical values in the constrained column(s).
• Unlike a primary key, a table can have many UNIQUE constraints, and the columns may allow NULLs.
• DB2 enforces a UNIQUE constraint by creating a unique index behind the scenes.

UNIQUE vs PRIMARY KEY

• A table can have only one primary key, but it can have several UNIQUE constraints.
• Primary key columns cannot be NULL; UNIQUE columns can allow NULLs (with DB2's one-NULL rule for the backing index).
• Use PRIMARY KEY for the main row identifier; use UNIQUE for other columns that must stay distinct, like an email address or employee badge number.
• Both reject duplicate values on INSERT and UPDATE in exactly the same way.

Example

• Column-level UNIQUE:-
  
  CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(25), SALARY DECIMAL(18,2), PRIMARY KEY (ID));
• Named table-level UNIQUE on multiple columns:-
  
  ALTER TABLE CUSTOMERS ADD CONSTRAINT UQ_CUST_AGE_SAL UNIQUE (AGE, SALARY);
• Drop a UNIQUE constraint:-
  
  ALTER TABLE CUSTOMERS DROP CONSTRAINT UQ_CUST_AGE_SAL;