Creating Primary Key Constraints- Tables and Constraints

When you implement a database, most tables you create require a primary key constraint to guarantee that every record in the table can be uniquely identified. There are multiple techniques for adding a primary key constraint to a table. The first example creates the primary key inline with the column definition:

SQL> create table dept( dept_id number primary key ,dept_desc varchar2(30));

If you select the CONSTRAINT_NAME from USER_CONSTRAINTS, note that Oracle generates a cryptic name for the constraint (such as SYS_C003682). Use the following syntax to explicitly give a name to a primary key constraint:

SQL> create table dept(
dept_id number constraint dept_pk primary key using index tablespace users, dept_desc varchar2(30));

Note When you create a primary key constraint, Oracle also creates a unique index with the same name as the constraint.You can control which tablespace the unique index is placed in via the USING INDEX TABLESPACE clause.

You can also specify the primary key constraint definition after the columns have been defined. The advantage of doing this is that you can define the constraint on multiple columns. The next example creates the primary key when the table is created, but not inline with the column definition:

SQL> create table dept( dept_id number, dept_desc varchar2(30),

constraint dept_pk primary key (dept_id) using index tablespace users);

If the table has already been created and you want to add a primary key constraint, use the ALTER TABLE statement. This example places a primary key constraint on the DEPT_ID column of the DEPT table:

SQL> alter table dept add constraint dept_pk primary key (dept_id) using index tablespace users;

When a primary key constraint is enabled, Oracle automatically creates a unique index associated with the primary key constraint. Some DBAs prefer to first create a nonunique index on the primary key column and then define the primary key constraint:

SQL> create index dept_pk on dept(dept_id) tablespace users;

SQL> alter table dept add constraint dept_pk primary key (dept_id);

The advantage of this approach is that you can drop or disable the primary key constraint independently of the index. When you are working with large data sets, you may want that sort of flexibility. If you do not create the index before creating the primary key constraint, then whenever you drop or disable the primary key constraint, the index is automatically dropped.

Confused about which method to use to create a primary key? All the methods are valid and have their merits. I’ve used all these methods to create primary key constraints. Usually, I use the ALTER TABLE statement, which adds the constraint after the table has been created.