3.2 UNIQUE

In this section we use a table that stores student information. The table description is:

 Name                           Null?    Type ------------------------------ -------- ------------ STUDENT_ID                              VARCHAR2(10) STUDENT_NAME                            VARCHAR2(30) COLLEGE_MAJOR                           VARCHAR2(15) STATUS                                  VARCHAR2(15) STATE                                   VARCHAR2(2) LICENSE_NO                              VARCHAR2(30) 

The UNIQUE constraint is applied to a column, or set of columns , to enforce the following rule: If a value exists, than that value must be unique.

This definition sounds similar to a PRIMARY KEY constraint. The following is a comparison between the primary key and unique constraints.

• A table can have just one primary key constraint but a table can have more than one unique constraint.

• A column that is part of a primary key can never have a NULL value. A column that is part of a unique constraint can be null. If a column has a unique constraint, there can be many rows with a NULL for that column. The values that are not null must be unique.

• When we create a primary key we create or reuse an index. The same is true for UNIQUE constraints.

• The primary key and unique constraint columns can be the parent in a foreign key relationship. The columns of a foreign key constraint frequently refer back to columns of a primary key constraint. They can also refer back to columns of a unique constraint.

The following DDL creates a concatenated unique constraint on columns (A, B):

 CREATE TABLE temp (pk NUMBER PRIMARY KEY, a NUMBER, b NUMBER); ALTER TABLE temp ADD CONSTRAINT uk_temp_a_b UNIQUE (a, b); 

NULLs are permitted in any column provided the data that does exist qualifies as unique. The following INSERT statements are valid.

 -- UNIQUE Constraint on last 2 columns. INSERT INTO temp VALUES (1,    1,    1); INSERT INTO temp VALUES (2,    2,    1); INSERT INTO temp VALUES (3,    1,    2); INSERT INTO temp VALUES (4, NULL, NULL); INSERT INTO temp VALUES (5, NULL, NULL); INSERT INTO temp VALUES (6,    1, NULL); INSERT INTO temp VALUES (7, NULL,    1); INSERT INTO temp VALUES (8,    2, NULL); 

The following duplicates the last insert and raises a constraint violation.

  SQL>  insert into temp values (9, 2, NULL);  insert into temp values (9, 2, NULL)   *   ORA-00001: unique constraint (SCOTT.UK_TEMP_A_B) violated  

Notice that the prefix "ORA" and a minus 1 for the error code are identical to the primary key constraint violation.

3.2.1 Combining NOT NULL, CHECK with UNIQUE Constraints

A NOT NULL constraint is sometimes added to the UNIQUE constraint. This additional requirement stipulates that the column values must be unique and that NULLs are not allowed. The following illustrates this case.

 CREATE TABLE temp (pk NUMBER PRIMARY KEY, a NUMBER NOT NULL); ALTER TABLE temp ADD CONSTRAINT uk_temp_a UNIQUE (a); 

Concatenated unique constraints are often supplemented with a CHECK constraint that prevents the condition: one column is NULL and one column has a value. This is enforced following this example.

 CREATE TABLE temp (pk NUMBER PRIMARY KEY, a NUMBER, b NUMBER); ALTER TABLE temp ADD CONSTRAINT uk_temp_a_b UNIQUE (a, b); ALTER TABLE temp ADD CONSTRAINT ck_temp_a_b CHECK     ((a IS NULL AND B IS NULL) OR      (a IS NOT NULL AND b IS NOT NULL)); 

The aforementioned CHECK and UNIQUE constraint combination allows the first two inserts, but rejects the second two inserts .

 INSERT INTO temp VALUES (6,    1,    1);  -- successful INSERT INTO temp VALUES (7, NULL, NULL);  -- successful INSERT INTO temp VALUES (6,    1, NULL);  -- fails INSERT INTO temp VALUES (7, NULL,    1);  -- fails 

Combining NOT NULL and CHECK constraints with UNIQUE constraints allows for several options.

• The column values, when data is present, must be unique. This is enforced with the UNIQUE constraint.

• Any column or all columns in the UNIQUE constraint can be mandatory with NOT NULL constraints on individual columns.

• Combinations of NULL and NOT NULL restrictions can be applied using a CHECK constraint and Boolean logic to dictate the rule.

3.2.2 Students Table Example

Consider a table, STUDENTS, that includes columns for student driver's license information. Not every student has a license ”this has to be considered . Our rules are:

• Every student must have a unique identification number or, STUDENT_ID ”this is the primary key.

• If a student has a driver's license the concatenation of license number and state abbreviation must be unique. The columns for state and license are not mandatory and do not have a NOT NULL constraint.

• The final CHECK constraint ensures that should a student have a license, the state and license values are both entered into the system.

The following DDL creates the STUDENTS table with a concatenated UNIQUE constraint on STATE and LICENSE_NO.

 CREATE TABLE students  (student_id    VARCHAR2(10) NOT NULL,   student_name  VARCHAR2(30) NOT NULL,   college_major VARCHAR2(15) NOT NULL,   status        VARCHAR2(15) NOT NULL,   state         VARCHAR2(2),   license_no    VARCHAR2(30)) TABLESPACE student_data; ALTER TABLE students   ADD CONSTRAINT pk_students PRIMARY KEY (student_id)   USING INDEX TABLESPACE student_index; ALTER TABLE students   ADD CONSTRAINT uk_students_license   UNIQUE (state, license_no)   USING INDEX TABLESPACE student_index; ALTER TABLE students    ADD CONSTRAINT ck_students_st_lic    CHECK ((state IS NULL AND license_no IS NULL) OR           (state IS NOT NULL AND license_no is NOT NULL)); 

The following paragraphs summarize key points about the above DDL.

• We call the primary key constraint PRIMARY KEY. The unique constraint is just "UNIQUE." In conversation we often refer to "unique key" constraints, but when writing DDL, leave off the "KEY" part. Also, unique constraints are often named with a prefix of "UK." The primary key, foreign key, and unique constraint all work together to enforce referential integrity. But the DDL syntax for a unique constraint does not include the "KEY" keyword.

• The unique constraint causes an index to be created; therefore, we have included the tablespace as the location for this index (if an index on these columns has already been created, then the constraint will use that index). For all the DDL in this text, the tables are created in a STUDENTS_DATA tablespace and all indexes are created in a STUDENTS_INDEX tablespace ”a fairly standard practice.

• The unique constraint is named UK_STUDENTS_LICENSE. Constraint names are limited to 30 characters. Constraint names in this text are preceded with a prefix to indicate the constraint type. This is followed by the table name. For a primary key constraint, that is all we need. For other constraints, we try to append the column name ”this can be difficult because table names and column names may be up to 30 characters . Sometimes you must abbreviate. Most important, the name of the constraint should clearly indicate the constraint type and table. The column names included in the constraint can be short abbreviations ”this approach helps when resolving an application constraint violation.

3.2.3 Deferrable and NOVALIDATE Options

Similar to primary key constraints, a UNIQUE constraint can be declared as deferrable. The constraint can be disabled and enabled with ALTER TABLE statements. All the options described in Section 3.1.9, "Deferrable Option," and 3.1.10, "NOVALIDATE," are applicable to UNIQUE constraints.

3.2.4 Error Handling in PL/SQL

A duplicate insert, for a primary key and unique constraint, is captured with the PL/SQL built-in exception. The exception name is

 DUP_VAL_ON_INDEX 

The following procedure inserts a student and captures unique constraint errors. The code for a check constraint error is also captured. The check constraint error number is mapped to a declared exception. The procedure then includes a handler for that exception. The primary key and unique constraint errors have the predefined exception declared in the language. Other types of errors, such as check constraint errors, need exceptions declared that must be mapped to the Oracle error number. The check constraint error number is minus 2290.

 CREATE OR REPLACE PROCEDURE     insert_student(v_student_id VARCHAR2,         v_student_name VARCHAR2,         v_college_major VARCHAR2,         v_status VARCHAR2,         v_state VARCHAR2,         v_license_no VARCHAR2) IS     check_constraint_violation exception;     pragma exception_init(check_constraint_violation, -2290); BEGIN     INSERT INTO students VALUES         (v_student_id, v_student_name,          v_college_major, v_status,          v_state, v_license_no);     dbms_output.put_line('insert complete'); EXCEPTION     WHEN DUP_VAL_ON_INDEX THEN         dbms_output.put_line('PK or unique const violation');     WHEN check_constraint_violation THEN         dbms_output.put_line('check constraint violation'); END; 

To test the error handling logic, use SQL*Plus to EXECUTE the procedure with some data. The first two inserts fail because the state and license violate the check constraint rule: both are NULL or both are NOT NULL.

The third and fourth inserts work. The last insert fails because it violates the unique constraint, which is a duplicate of the prior insert.

 insert_student('A900','Ann','Math','Degree','CA',NULL);  check constraint violation  insert_student('A900','Ann','Math','Degree',NULL,'ABC');  check constraint violation  insert_student('A900','Ann','Math','Degree',NULL,NULL);  insert complete  insert_student('A902','Joe','Math','Degree','CA','ABC');  insert complete  insert_student('A903','Ben','Math','Degree','CA','ABC');  PK or unique const violation