UNIQUE Constraint Example

Type Of UNIQUE Constraint

Type Of UNIQUE Constraint	Example
Column Level UNIQUE Constraint	CREATE TABLE SampUNQ01(SampID NUMBER(2) CONSTRAINT SampUNQ01_SampID_UNQ UNIQUE,SampName VARCHAR2(10),SampDate DATE);
Table Level UNIQUE Constraint	CREATE TABLE SampUNQ02 (SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SampUNQ02_SampID_UNQ UNIQUE(SampID));
Composite UNIQUE Constraint(Always declare in Table level)	CREATE TABLE SampUNQ05(SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SampUNQ05_SampIDName_UNQ UNIQUE(SampID, SampName));

Now i am trying to insert data in Column level UNIQUE Constraint table SampUNQ01 , UNIQUE
key is SampID .

SQL> INSERT INTO SampUNQ01 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SampUNQ01 VALUES(1, 'SAMPLE02', SYSDATE);

INSERT INTO SampUNQ01
 *
 ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPUNQ01_SAMPID_UNQ) violated

We can insert NULL into UNIQUE key column

SQL>INSERT INTO SampUNQ01 VALUES(NULL, 'SAMPLE02', SYSDATE);

SQL>INSERT INTO SampUNQ01 VALUES(NULL, 'SAMPLE03', SYSDATE);

Now i am trying to insert data in Table level UNIQUE Constraint table SampUNQ02 , UNIQUE
key is SampID .

Insert same data by changing the table name.

Now i am trying to insert data in Composite UNIQUE Constraint table SampUNQ05, Composite UNIQUE key SampID, SampName .

SQL>INSERT INTO SampUNQ05 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(1, 'SAMPLE02', SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(2, 'SAMPLE02', SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE03', SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE04', SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE04', SYSDATE);

INSERT INTO SampUNQ05 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPUNQ05_SAMPIDNAME_UNQ) violated

SQL>INSERT INTO SampUNQ05 VALUES(NULL, NULL, SYSDATE);

1 row created.

SQL>INSERT INTO SampUNQ05 VALUES(NULL, NULL, SYSDATE);

1 row created.