Databases Reference
In-Depth Information
SQL> alter table cust drop primary key;
you'll receive this error
ORA-02273: this unique/primary key is referenced by some foreign keys
In this situation you'll need to first drop or disable the referenced foreign key, or use the
CASCADE
clause to automatically drop the foreign key constraint when the primary key constraint is dropped or
disabled. For example,
SQL> alter table cust drop constraint cust_pk cascade;
SQL> alter table cust disable constraint cust_pk cascade;
SQL> alter table cust drop primary key cascade;
Note
Cascading the dropping of constraints only drops any dependent foreign key constraints, it doesn't delete
any data from children tables.
Creating a B-tree Index on Unique Key Columns
The main purpose of a unique key constraint is to enforce uniqueness on columns that aren't part of the
primary key. If there is a business requirement that non-primary key columns be unique within a table,
then a unique key constraint should be used. For example, you may have a primary key defined on
CUST_ID
in the customer table, but may also require a unique key constraint on a combination of the
LAST_NAME
and
FIRST_NAME
columns.
Note
Unique keys differ from primary keys in two ways. First, a unique key can contain
NULL
values. Second,
there can be more than one unique key defined per table (whereas there can only be one primary key defined per
table).
If you have a requirement for a unique constraint on a column, you can implement this requirement
in several different ways.
Use the
ALTER TABLE
statement to create a unique constraint. This will
automatically create a unique B-tree index.
•
Use the
CREATE TABLE
statement to create a unique constraint. This will
automatically create a unique B-tree index.
•
•
Create a B-tree index and constraint separately. Use this approach if you want to
manage the index and constraint separately when disabling or dropping the
constraint.
