Using Primary Keys in Oracle

This article is written about how to create, drop, disable, and allow a most important key in Oracle with syntax and examples.

What is a primary key in Oracle?

In Oracle, a essential key is a single discipline or mixture of fields that uniquely defines a record. None of the fields that are part of the predominant key can comprise a null value. A desk can have solely one primary key.

Note

In Oracle, a major key can no longer comprise more than 32 columns. A major key can be defined in either a CREATE TABLE announcement or an ALTER TABLE statement.

Create Primary Key – Using CREATE TABLE statement

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

Syntax

The syntax to create a important key the usage of the CREATE TABLE declaration 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 predominant key the usage of 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 got created a primary key on the provider table referred to as supplier_pk. It consists of solely one area – the supplier_id field.

We ought to additionally create a foremost key with greater than one area as in the instance 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 essential key in Oracle with the ALTER TABLE statement.

Syntax

The syntax to create a important key the usage of the ALTER TABLE declaration in Oracle/PLSQL is:

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

Example

Let’s seem at an example of how to create a foremost key the usage of the ALTER TABLE announcement in Oracle.

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

In this example, we have created a essential key on the existing provider desk known as supplier_pk. It consists of the area referred to as supplier_id.

We may want to also create a main key with extra than one area as in the instance below:

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

Drop Primary Key

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

Syntax

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

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Let’s seem to be at an example of how to drop a most important key the usage of the ALTER TABLE assertion in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we’re shedding a important key on the dealer table known as supplier_pk.

Disable Primary Key

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

Syntax

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

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

Let’s look at an instance of how to disable a primary the use of the ALTER TABLE assertion in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we’re disabling a important key on the supplier desk known as supplier_pk.

Enable Primary Key

You can allow a major key in Oracle the use of the ALTER TABLE statement.

Syntax

The syntax to allow a main key using the ALTER TABLE declaration in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

Let’s seem at an instance of how to enable a foremost key using the ALTER TABLE announcement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we’re enabling a important key on the supplier desk referred to as supplier_pk.