In focus

How To Use Primary Key In Oracle

In this article you will learn how to create , drop, disable and enable a primary key in Oracle with syntax and example.

Kartik Kumar May 31, 2016

Primary key in oracle

In Oracle, a primary key is a single field or combination of fields. A primary key in a relational database is unique. None of the fields that are part of the primary key can contain a null value. There is only one primary key in a table.

A primary key cannot contain more than 32 columns.

A primary key can be defined in either a create table statement or alter table statement.

Create table statement using Primary key

You can create a primary key in a create table statement.

Syntax:

Create table table_name
(
Column 1 datatype(),
Column 2 datatype(),
. .
. .
. .
Column n datatype()
Constraint constraint_name primary key(column 1, column 2…..column n)
);
Commit;

Example

Look at an example, we can create a primary key in create table statement.

Create table customer
(
Customer_id number(20),
Customer_name varchar2(15),
Customer_address varchar2(100),
Customer_mob number(10),
Constraint pk_customer_id primary key (customer_id)
);
Commit;



Drop Primary key

You can drop a primary key using the alter table statement.

Syntax:

Drop a primary key using alter table statement.

alter table table_name
Drop constraint constraint_name;

Example

Alter table customer
Drop constraint pk_customer_id;
Commit;



Disable Primary Key

You can disable the primary key using alter table statement.

Syntax:

Disable a primary key using alter table statement.

Alter table table_name
Disable constraint constraint_name;

Example

Alter table customer
Disable constraint pk_customer_id;
Commit;



Enable a primary key

You can enable a primary key usin an alter table statement.

Syntax:

Alter table table_name
Enable constraint constraint_name;

Example

Alter table customer
Enable constraint pk_customer_id;
Commit;

oracle primary key

COMMENT USING