Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Unique

The unique constraintensures that one or more columns defined contain unique values.  If more than one column is in the constraint, then each column can have duplicate values as long as the combination of values is unique.   

For example, if we place a unique constrainton the author’s first and last names, then I can have multiple authors with first name John and last name Miller, but I can have only one John Miller.  John Smith, Fred Miller, Steve Miller, and John Jones all will be allowed, but only one of each first, last name pair. 

SQL> alter table author
  2    add (constraint names_un unique (author_first_name,author_last_name));

 Table altered. 

A quick check of the indexes on the AUTHOR table will show that the constraint is being enforced using an index.

 SQL> select
  2    index_name,
  3    table_name
  4  from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
NAMES_UN                       AUTHOR
AUTHOR_PK                      AUTHOR

We can create a unique constraintwhen we create the table.

SQL> create table editor
  2  (
  3    editor_key               varchar2(9) not null,
  4    editor_last_name         varchar2(40),
  5    editor_first_name        varchar2(30),
  6    editor_hire_date         date,
  7    editor_active            char(1) 
  8    constraint active_ck check (editor_active in ('Y','N')),
  9    constraint ed_name_un unique
(editor_first_name,editor_last_name),
 10    constraint editor_pk primary key (editor_key)
 11      using index tablespace users
 12  );

Table created. 

As with the primary key, we can DISABLE, ENABLE and DROP the constraint.

 SQL> alter table editor disable constraint ed_name_un; 

Table altered.

 SQL> alter table editor enable constraint ed_name_un;

 Table altered.

 SQL> alter table editor drop constraint ed_name_un;

 Table altered.

As with the primary key, the index used to enforce the constraint is dropped whenever the constraint is disabled or dropped.  When the constraint is enabled again, the index is rebuilt.

You can also create a unique constraintby creating a unique index. 

create unique index contract_uk on
            author (author_contract_nbr); 

The unique index performs the same function as a unique constraint ; however, there is no constraint name.  The index is the constraint.  To remove the constraint, drop the index.  There is more on indexes at the end of the chapter.

The constraints we have covered so far all impact data being inserted or updated against data already in that column.  The foreign key constraintvalidates data against data in another column or a column in another table.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.