Databases Reference
In-Depth Information
Many of the columns in the primary key are frequently used within the
WHERE
clause of queries accessing the application. An index on these columns will
improve the query performance.
•
•
Oracle won't allow you to create foreign key constraints on a child table unless a
primary key or unique key constraint has been defined for the parent table.
Therefore, if you require foreign key constraints, you must use primary key or
unique key constraints.
Oracle requires a corresponding index for any enabled primary key. There are several techniques for
creating a primary key constraint and its corresponding index.
First, create the table. Then, in a separate
ALTER TABLE
statement, add the primary
key constraint. The
ALTER TABLE
statement creates both the primary key constraint
and an index.
•
•
Specify the primary key constraint inline (with the column) or out-of-line in a
CREATE TABLE
statement.
Create the table, then use a
CREATE INDEX
statement to create an index that
contains the primary key columns, and then use
ALTER TABLE...ADD CONSTRAINT
to
add the primary key constraint.
We will show examples of each of these techniques in the following subsections.
•
Use ALTER TABLE to Create a Primary Key Constraint and Index
In our opinion, the technique shown next is the most preferable method for creating a primary key
constraint and the associated index. This approach allows you to manage the table creation separate
from the constraint and index definition. When you work with applications that contain thousands of
tables, constraints, and indexes, it's often easier to manage and diagnose installation issues when you
separate the creation of tables from corresponding constraints and indexes. This isn't a written-in-stone
guideline; rather, it's a preference that has evolved from troubleshooting problems.
In this example, the primary key constraint is created separately from the table creation. First, the
table is created without any constraint definitions.
create table cust(
cust_id number
,first_name varchar2(200)
,last_name varchar2(200));
Now a primary key constraint is added.
alter table cust
add constraint cust_pk
primary key (cust_id)
using index tablespace users;
In this code example, the
ALTER TABLE...ADD CONSTRAINT
statement creates both a primary key
constraint and a unique index. Both the constraint and index are named
CUST_PK
.
