Oracle PL/SQL: Primary keys

What is a primary key?

A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

Note: In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

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

For example:

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’ve created a primary key on the supplier table called supplier_pk. It consists of only one field – the supplier_id field.

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)

);

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, … column_n);

For example:

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

In this example, we’ve created a primary key on the existing supplier table called supplier_pk. It consists of the 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);

Drop a Primary Key

The syntax for dropping a primary key is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

In this example, we’re dropping a primary key on the supplier table called supplier_pk.

Disable a Primary Key

The syntax for disabling a primary key is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
disable CONSTRAINT supplier_pk;

In this example, we’re disabling a primary key on the supplier table called supplier_pk.

Enable a Primary Key

The syntax for enabling a primary key is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;

In this example, we’re enabling a primary key on the supplier table called supplier_pk.

Related Posts

1. Oracle PL/SQL: Sequences (Autonumber)
2. Oracle PL/SQL: Is Null / Is Not Null
3. Oracle PL/SQL: Loops and Conditional Statements
4. Oracle PL/SQL: Cursors
5. Oracle PL/SQL: Advanced Cursors

Tags: oracle plsql, oracle tutorial, plsql, plsql tutorial