Databases Reference
In-Depth Information
If you're asked to troubleshoot this issue, the first place to look is in
DBA_CONSTRAINTS
for a constraint
named in the error message. However, there is no information.
select
constraint_name
from dba_constraints
where constraint_name='CUST_UK1';
no rows selected
The “no rows selected” message can be confusing: the error message thrown when you insert into
the table indicates that a unique constraint has been violated, yet there is no information in the
constraint-related data-dictionary views. In this situation, you have to look at
DBA_INDEXES
to view the
details of the unique index that has been created. For example,
select index_name, uniqueness
from dba_indexes where index_name='CUST_UK1';
Here's some sample output:
INDEX_NAME UNIQUENES
-------------------- ---------
CUST_UK1 UNIQUE
If you want to be able to use the constraint related data dictionary views to report on unique key
constraints, you should also define a constraint.
Dropping a Unique Key Constraint and Index
If an index was automatically created when the unique key constraint was created, then you can't
directly drop the index. In this scenario, you must drop or disable the unique key constraint and the
associated index will automatically be dropped. For example,
SQL> alter table cust drop constraint cust_uk1;
This line drops both constraint and the index. If you want to keep the index, then specify the
KEEP
INDEX
clause.
SQL> alter table cust drop constraint cust_uk1 keep index;
If you created the index and unique key constraint separately, or if there is no unique key constraint
associated with a unique index, then you can directly drop the index.
Indexing Foreign Key Columns
Foreign key constraints ensure that when inserting into a child table, a corresponding parent table
record exists. This is the mechanism to guarantee that data conforms to parent/child business
relationship rules. Foreign keys are also referred to as referential integrity constraints.
