The UNIQUE KEY constraint is a rule regarding unique column values. It is defined on a specific column or a column set and ensures that the values of the columns or column sets in the rows to be inserted or updated are unique. This constraint ensures that no two rows in a table have the same values for a specific column or column set.
This topic introduces the features of the UNIQUE KEY constraint through the following specific examples.
obclient> CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT t1_uk_c1_c2 UNIQUE(c1, c2));
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES(1, 1);
ORA-00001: unique constraint '1-1' for key 'T1_UK_C1_C2' violated
obclient> INSERT INTO t1 VALUES(null, 1);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES(null, 1);
ORA-00001: unique constraint 'NULL-1' for key 'T1_UK_C1_C2' violated
obclient> INSERT INTO t1 VALUES(null, null);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES(null, null);
Query OK, 1 row affected
In the following figure, a UNIQUE KEY constraint is defined on the DNAME column of the DEPT table. In this case, duplicate department names are not allowed in this table, but you can insert null values into the DNAME column. If a NOT NULL constraint is also defined on the DNAME column, you cannot insert null values into the DNAME column.
If a UNIQUE KEY consists of multiple columns, this set of columns is referred to as a composite unique key. In the example shown below, the UNIQUE KEY constraint defined on the CUSTOMER table uses a composite unique key, consisting of the AREA and PHONE columns. This requires that the values of the key in any two rows cannot be duplicate. You can input null values in these columns. However, if the columns are also defined with a NOT NULL constraint, null values are not allowed.
You can insert any number of records into the CUSTOMER table. However, according to the constraints imposed by UNIQUE KEY, the combination of area code and telephone number in each row must be unique. This helps to prevent issues with duplicate phone numbers caused by oversight.
