Database Tutorial

UNIQUE constraints are available in all relational databases. UNIQUE constraints let you check whether the value of a column or set of columns are unique within all the rows of a table. Every well defined table should have one or more columns that uniquely identifies every row of the table. That column or set of columns is the natural key.

After you identify the natural key, you should create a unique constraint across the column or set of columns. You can create a unique constraint with the CREATE or ALTER statement. The CREATE statement is the easiest because you don’t need to know if there are existing rows in the table. The ALTER statement can only work when the column or set of columns identified as the natural key contains unique values.

A single column may include one row that is null. A set of columns may include a unique set of values in and the combination of values must be unique across all rows in a table.

Oracle lets you implement a UNIQUE constraint in a CREATE statement as either an in-line or out-of-line constraint. You may use the in-line syntax when there’s only one unique column but you must use a table-level or out-of-line when the unique constraint applies across more than one column. The alter command works when you have one column or a set of columns in a unique constraint.

• CREATE Statement:
• You can define a UNIQUE constraint on the same line as you define a single column. This approach is called placing an inline constraint on a column.

  The following sample table has two named inline constraints:

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck UNIQUE);

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck UNIQUE);

  The first inline constraint is a PRIMARY KEY constraint on the sample_id column. You should note that the PRIMARY KEY constraint makes a column applies three rules. It verifies the sample_id column is mandatory and unique in the set of rows inside the sample table, and designates the sample_id column as a primary key that you can refer to from foreign key columns.

  The second is a UNIQUE constraint that makes the sample_text column value unique within a table. Any attempt to add a row with a duplicate value in the sample_text column would return the following error:

  * ERROR AT line 1: ORA-00001: UNIQUE CONSTRAINT (STUDENT.SAMPLE_PK) violated

  * ERROR at line 1: ORA-00001: unique constraint (STUDENT.SAMPLE_PK) violated

  If you describe the sample table with the DESCRIBE command in SQL*Plus, you’ll see:

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT VARCHAR2(20)

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT VARCHAR2(20)

  If you want to preclude null values in the sample_text column and guarantee its uniqueness, you must use an out-of-line constraint, like the following:

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL , CONSTRAINT sample_ck UNIQUE (sample_text));

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL , CONSTRAINT sample_ck UNIQUE (sample_text));

  If you describe the sample table with the DESCRIBE command in SQL*Plus, you’ll see:

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT NOT NULL VARCHAR2(20)

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT NOT NULL VARCHAR2(20)

• You can define a table-level or out-of-line UNIQUE constraint when the table requires a multiple column unique constraint where some of the columns are optional and others are mandatory. For this section, you will use the customer table. You can create the customer table with a unique key across the first_name, middle_name, and last_name columns with the following syntax:

  CREATE TABLE customer ( contact_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , username VARCHAR2(30) CONSTRAINT customer_nn1 NOT NULL , first_name VARCHAR2(20) CONSTRAINT customer_nn2 NOT NULL , middle_name VARCHAR2(20) , last_name VARCHAR2(20) CONSTRAINT customer_nn3 NOT NULL , CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name));

  CREATE TABLE customer ( contact_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , username VARCHAR2(30) CONSTRAINT customer_nn1 NOT NULL , first_name VARCHAR2(20) CONSTRAINT customer_nn2 NOT NULL , middle_name VARCHAR2(20) , last_name VARCHAR2(20) CONSTRAINT customer_nn3 NOT NULL , CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name));

  The new customer table now only accepts unique combinations of the first_name, middle_name, and last_name columns.
  .

• ALTER Statement:
• You can add or drop a UNIQUE constraint when you want to add a customer_uq constraint that includes the first_name, middle_name, and last_name columns.

  You drop an existing customer_uq UNIQUE constraint with the following syntax:

  ALTER TABLE customer DROP CONSTRAINT customer_uq;

  ALTER TABLE customer DROP CONSTRAINT customer_uq;

• After you drop the customer_uq UNIQUE constraint, you can add a new version of the customer_uq UNIQUE constraint with the following syntax:

  ALTER TABLE customer ADD CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name);

  ALTER TABLE customer ADD CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name);