Databases Reference
In-Depth Information
Use CREATE TABLE to Create a Primary Key Constraint and Index
Another common way to create a primary key constraint and index is with the
CREATE TABLE
statement.
You can directly specify a constraint inline (with the column). The advantage of this approach is that it's
very simple. If you're experimenting in a development or test environment, this approach is quick and
effective. One downside to this approach is that it doesn't allow for a primary key to be defined on
multiple columns. Here's an example:
create table cust(
cust_id number primary key
,first_name varchar2(30)
,last_name varchar2(30));
In this code, Oracle creates both a primary key constraint and a corresponding unique index. Oracle
automatically generates a random name like
SYS_C123456
(both the constraint and index are given the
same name).
If you want to explicitly provide a name (for the constraint and index), you can do so as follows:
create table cust(
cust_id number constraint cust_pk primary key
,first_name varchar2(30)
,last_name varchar2(30));
You can also specify the placement of the tablespace for the index as shown:
create table cust(
cust_id number constraint cust_pk primary key
using index tablespace users
,first_name varchar2(30)
,last_name varchar2(30));
You can also define the primary key constraint out-of-line (from the column) within the
CREATE
TABLE
statement. Here's an example of defining a primary key constraint out-of-line:
create table cust(
cust_id number
,first_name varchar2(30)
,last_name varchar2(30)
,constraint cust_pk primary key (cust_id)
using index tablespace users);
This technique is called out-of-line because the constraint declaration is separated (by a comma)
from the column definition. The out-of-line approach has one advantage over the inline approach in
that you can specify multiple columns for the primary key.
