How to Use Oracle PRIMARY KEY Constraint to Manage Primary Keys

Summary: in this tutorial, you will learn how to use Oracle PRIMARY KEY constraint to manage the primary key of a table.

Introduction to the primary key

A primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.

The following are rules that make a column a primary key:

A primary key column cannot contain a NULL value or an empty string.
A primary key value must be unique within the entire table.
A primary key value should not be changed over time.

According to these rules, the following are the recommendations for the primary keys:

First, the primary key should be meaningless. Sometimes, you may want use meaningful data, which considers being unique, for the primary keys e.g., social security number (SSN), vehicle identification number (VIN), email, and phone number. However, you don’t know when the email or phone number changes or is reused by another person. In such cases, it will create many data problems. In the database world, the artificial keys are known as surrogate keys which are as opposed to natural primary keys.
Second, the primary keys should be compact. The primary keys typically are numeric because Oracle typically processes numbers faster than any other data types.

It is considered a best practice have a primary key in every table though it is not mandatory in Oracle.

To create a primary key in a table, you use the PRIMARY KEY constraint.

Oracle PRIMARY KEY constraint examples

Typically, you create a primary key for a table when you create that table. In addition, you can add a primary key to a table after the fact by using the ALTER TABLE statement.

Creating a primary key that consists of one column

The following CREATE TABLE statement creates the purchase_orderstable:

CREATE TABLE purchase_orders (
    po_nr NUMBER PRIMARY KEY,
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL 
);
Code language: SQL (Structured Query Language) (sql)

The purchase_orders table has four columns purchase order number (po_nr), vendor id (vendor_id), purchase order status (po_status), and the timestamp (created_at) of which the purchase order is created.

In this table, defined the po_nr column as the primary key by using the PRIMARY KEY clause.

Note that the PRIMARY KEY clause implicitly makes the po_nrcolumn NOT NULL so you don’t have to define the column like:

po_nr NUMBER NOT NULL PRIMARY KEY
Code language: SQL (Structured Query Language) (sql)

The PRIMARY KEY constraint in this example is an inline constraint because it is on the same line as the po_nr column.

Consider the following statement.

CREATE TABLE purchase_orders (
    po_nr NUMBER,
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
);
Code language: SQL (Structured Query Language) (sql)

This example used the PRIMARY KEY constraint as the table constraint. Notice the following clause:

CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
Code language: SQL (Structured Query Language) (sql)

In addition, we explicitly assigned the PRIMARY KEY constraint a name pk_purchase_orders.

Creating a primary key that consists of multiple columns

The following statement creates the purchase order line items table:

CREATE TABLE purchase_order_items (
    po_nr NUMBER NOT NULL,
    item_nr NUMBER NOT NULL,
    product_id NUMBER NOT NULL,  
    quantity NUMBER NOT NULL,
    purchase_unit NUMBER NOT NULL,
    buy_price NUMBER (9,2) NOT NULL,
    delivery_date DATE,
    PRIMARY KEY (po_nr, item_nr)
);
Code language: SQL (Structured Query Language) (sql)

In this example, the primary key of the purchase_order_items table consists of two columns: po_nr and item_nr. It means that the combination of values of these columns uniquely identifies a purchase order line item.

This example did not use the CONSTRAINT clause to explicitly assign the PRIMARY KEY constraint a name. Therefore, Oracle implicitly assigned the primary key constraint a system-generated name such as SYS_C0010617.

Adding a primary key to a table

Sometimes, you may want to add a primary key constraint to an existing table. To do it, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
PRIMARY KEY (column1, column2, ...);
Code language: SQL (Structured Query Language) (sql)

The following example creates the vendors table first and then adds a primary key constraint to it:

CREATE TABLE vendors (
    vendor_id NUMBER,
    vendor_name VARCHAR2(255) NOT NULL,
    address VARCHAR2(255) NOT NULL
);

ALTER TABLE vendors 
ADD CONSTRAINT pk_vendors PRIMARY KEY (vendor_id);
Code language: SQL (Structured Query Language) (sql)

Dropping an Oracle PRIMARY KEY constraint

You will rarely drop a PRIMARY KEY constraint from a table. If you have to do so, you use the following ALTER TABLE statement.

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;
Code language: SQL (Structured Query Language) (sql)

For example, you can drop the primary key constraint of the vendors table as follows:

ALTER TABLE vendors
DROP CONSTRAINT pk_vendors;
Code language: SQL (Structured Query Language) (sql)

It is possible to use the following statement to drop the primary key of a table:

ALTER TABLE table_name
DROP PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

For example:

ALTER TABLE vendors
DROP PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

Enable / Disable an Oracle PRIMARY KEY constraint

To improve the performance when loading a large amount of data into a table or updating mass data, you can temporarily disable the PRIMARY KEY constraint.

To disable a PRIMARY KEYconstraint of a table, you use the ALTER TABLE statement:

ALTER TABLE table_name
DISABLE CONSTRAINT primary_key_constraint_name;
Code language: SQL (Structured Query Language) (sql)

ALTER TABLE table_name
DISABLE PRIMARY KEY;Code language: SQL (Structured Query Language) (sql)

For example, to disable the primary key constraint of the purchase_orders table, you use the following statement:

ALTER TABLE purchase_orders
DISABLE CONSTRAINT pk_purchase_orders;
Code language: SQL (Structured Query Language) (sql)

ALTER TABLE purchase_orders
DISABLE PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

To enable a primary key constraint, you use the following ALTER TABLE statement:

ALTER TABLE table_name
ENABLE CONSTRAINT primary_key_constraint_name;
Code language: SQL (Structured Query Language) (sql)

ALTER TABLE table_name
ENABLE PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

The following example enables the PRIMARY KEY constraint of the purchase_orders table:

ALTER TABLE purchase_orders
ENABLE CONSTRAINT pk_purchase_orders;
Code language: SQL (Structured Query Language) (sql)

ALTER TABLE purchase_orders
ENABLE PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use Oracle PRIMARY KEY constraint to create, add, disable, enable, and drop a primary key of a table.

Was this tutorial helpful?