Databases Reference
In-Depth Information
This next example creates a unique constraint using the out-of-line technique on the combination
of the
FIRST_NAME
and
LAST_NAME
columns:
create table cust(
cust_id number constraint cust_pk primary key
using index tablespace users
,first_name varchar2(30)
,last_name varchar2(30)
,ssn varchar2(15)
,constraint cust_uk1 unique (first_name, last_name)
using index tablespace users);
The out-of-line definition has the advantage of allowing you to create a unique key constraint on
multiple columns.
Create a B-tree Index and Unique Key Constraint Separately
If you need to manage the index and constraint separately, then first create the index and then the
constraint. For example,
SQL> create unique index cust_uk1 on cust(first_name, last_name) tablespace users;
SQL> alter table cust add constraint cust_uk1 unique(first_name, last_name);
The advantage of creating the index separate from the constraint is that you can drop or disable the
constraint without dropping the underlying index. When working with big data, you may want to
consider this approach. If you need to disable the constraint for any reason and then re-enable it later,
you can do so without dropping the index (which may take a long time for large indexes).
Creating Only a Unique Index
You can also create just a unique index without adding the unique constraint. If you never plan on
referencing a unique key from a foreign key, then it's okay to only create a unique index without defining
the unique constraint. Here's an example of creating a unique index without an associated constraint:
SQL> create unique index cust_uk1 on cust(first_name, last_name) tablespace users;
When you create only a unique index explicitly (as in the prior statement), Oracle creates a unique
index but doesn't add an entry for a constraint in
DBA/ALL/USER_CONSTRAINTS
. Why does this matter?
Consider this scenario:
SQL> insert into cust values (1, 'JAMES', 'STARK');
SQL> insert into cust values (2, 'JAMES', 'STARK');
Here's the corresponding error message that is thrown:
ORA-00001: unique constraint (MV_MAINT.CUST_UK1) violated
