Oracle Primary Key Constraint

1. Overview

Data Integrity is one of the important dimension to be maintained in database. The article explains the implementation and use of Primary key constraint in Oracle.

2. Introduction

Primary key is one of the data integrity constraints which technically combine the UNIQUE and NOT NULL constraints. It does not allow the column value to be NULL or get duplicated.

1. A table can have only one primary key.
2. Multiple columns can be clubbed under composite primary key.
3. Oracle internally creates a unique index to prevent duplication in the column values.

3. Syntax

Column level:

Code :

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

Code :

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

4. Examples

4.1. The following example shows how to use PRIMARY KEY constraint at column level.

Code sql:

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ... );

4.2. The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

Code sql:

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID) 
 );

Constraints on a table can be queried from the dictionary view [ALL | DBA | USER]_CONSTRAINTS and USER_CONS_COLUMNS.

Below query lists the constraints contained by the table, their associated indexes (if any) and status (ENABLED or DISABLED).

Code sql:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME , STATUS
FROM USER_CONSTRAINTS  
WHERE TABLE_NAME = '<TABLE_NAME>';

5. Notes

1. Primary Key can be added/disabled/enabled using ALTER TABLE command. Single r Column or multiple columns can be specified in the syntax. In this case, constraint name is generated by the Oracle server in 'SYS_*' form.

Below ALTER command adds the primary key to a table

Code sql:

ALTER TABLE [TABLE NAME]
ADD PRIMARY KEY(COLUMN NAME)

Below ALTER command syntax enables/disables the primary key of a table

Code sql:

ALTER TABLE [TABLE NAME]
[ENABLE | DISABLE] PRIMARY KEY

Disabling the primary key does the below changes
• Primary key constraint is ineffective
• Oracle created unique Index is dropped
• Status is DISABLED in USER_CONSTRAINTS dictionary view

2. Primary key can be dropped using ALTER TABLE command. It drops the constraint and associated index.

Code sql:

ALTER TABLE
DROP RPIMARY KEY

An index can be associated to a primary key using ‘USING INDEX’ clause in the ALTER table.

Code sql:

ALTER TABLE [TABLE NAME] 
ADD CONSTRAINT [CONSTRAINT NAME] PRIMARY KEY(col_1)  
USING INDEX [INDEX AME];

An Index can be re-associated to an existing Primary key by disabling it and associating an index while enabling it.

Code sql:

ALTER TABLE [TABLE NAME]
ENABLE CONSTRAINT [CONSTRAINT NAME]
USING INDEX [INDEX NAME];

A non unique index can also be associated with a PK. Bitmap indexes cannot be associated with PK.
If a Primary key is dropped, explicitly associated indexes are not dropped. However, if the index is implicitly created by the oracle server during the creation of Primary Key constraint, it would be dropped during dropping and disabling of primary key. It can be retained using KEEP INDEX clause.

Oracle Primary Key Constraint

Article Categories