fastread homefastrread library fastread menu

Oracle/PLSQL : Primary Keys

Tutorial by:      Date: 2016-04-15 04:08:19

❰ Previous Next ❱

This Oracle tutorial explains how to create, drop, disable, and enable a primary key in Oracle with syntax and examples.

What is a primary key in Oracle?

In Oracle, 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.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name

(

  column1 datatype null/not null,

  column2 datatype null/not null,

  ...

 

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)

);

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

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)

);

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name

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

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

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 Primary Key

You can drop a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier

DROP CONSTRAINT supplier_pk;

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

Disable Primary Key

You can disable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier

DISABLE CONSTRAINT supplier_pk;

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

Enable Primary Key

You can enable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier

ENABLE CONSTRAINT supplier_pk;

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

 

❰ Previous Next ❱


Oracle/PLSQL

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website