Oracle Primary Keys

In Oracle PLSQL primary key is a single field or a combination of fields that determines the uniqueness of the record. Fields that are part of the primary key cannot contain the value NULL. A table can only have one primary key.

Note:

• In Oracle/PLSQL primary key cannot contain more than 32 columns.
• Primary key can be defined either in the CREATE TABLE operator or in the ALTER TABLE operator.

Create primary key – using CREATE TABLE

You can create a primary key in Oracle/PLSQL using the CREATE TABLE operator.

Syntax

CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
…
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Let’s consider an example of how to create a primary key using the Oracle/PLSQL CREATE TABLE operator:

CREATE TABLE supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we created the primary key table of the supplier with the name supplier_pk. It consists of only one field – supplier_id.

We could also create a primary key with more than one field, as in the example below:

CREATE TABLE supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

Create Primary Key – using ALTER TABLE

You can create a primary key in Oracle/PLSQL using ALTER TABLE.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Let’s consider an example of how to create a primary key in Oracle/PLSQL using the ALTER TABLE operator.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we created the primary key of an existing supplier table called supplier_pk. It consists of a field called supplier_id.

We could also create a primary key with more than one field, as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

Delete primary key

You can remove the primary key in Oracle/PLSQL using the ALTER TABLE operator.

Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Let’s consider an example of how to remove a primary key using the ALTER TABLE operator.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we remove the primary key with the name supplier_pk from the supplier table .

Disable primary key

You can disable the primary key in Oracle/PLSQL using the ALTER TABLE operator.

Syntax

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Let’s consider an example of how to disable a primary key using the ALTER TABLE operator in Oracle/PLSQL.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we disable the primary key table of the supplier called supplier_pk.

Enable primary key

You can enable primary key in Oracle/PLSQL using the ALTER TABLE operator.

Syntax

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Let’s consider an example of how to enable primary key with the ALTER TABLE operator in Oracle/PLSQL.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we include the primary key table of the supplier named supplier_pk.

SQL tutorials: SQL Primary Key constraint, Drop primary Key