Primary Key in Oracle

Primary Key explained by real life example

In your school or college, every student in a class is given a unique roll number. No student is without roll number and same roll number is not assigned to any two students. Concept of primary is mostly similar to the roll number system except that primary key is optional.

Here,
Class room = Table
Roll number = Primary key
A single student = Record or row of the table

Primary key in Oracle

A primary key is a single column or set of columns that uniquely identifies a record. A table may or may not have primary key. Whenever any column is defined as primary key it CANNOT contain null values. NOT NULL and UNIQUE constraint are by default created when you define a column or set of columns as primary key.

Some facts…

  • There can be only one primary key per table.
  • Maximum combination of 32 columns can only be taken as primary key in single table

Syntax

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

Primary key at table level

You can create a primary key in Oracle with the CREATE TABLE statement. This primary key is said to be created at table-level

Example

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

CREATE TABLE employee_master
  ( emp_id numeric(10),
  emp_name varchar2(50),
  contact_number varchar2(50),
  CONSTRAINT employee_pk PRIMARY KEY (emp_id) );

Lets create primary key on set of columns:

CREATE TABLE employee_master
  (
  emp_id numeric(10), 
  emp_name varchar2(50),
  contact_number varchar2(50),
  CONSTRAINT employee_pk PRIMARY KEY (emp_id, emp_name)
  );

Primary Key at column-level

A primary key can also defined at column level. Lets look how…

Example

Lets look at an example of how to create a primary key at column level using the CREATE TABLE

CREATE TABLE employee_master
   (
   emp_id numeric(10) PRIMARY KEY,
   emp_name varchar2(50),
   contact_number varchar2(50)
   );

Primary Key using ALTER statement

While creating table if you forgot to create primary key or sometimes if you planned to create it later on, you can use ALTER statement for that.

Syntax

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

Example

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

ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY (emp_id);

Here primary key is created on column emp_id.

Primary Key can also be created on multiple columns using ALTER statement

ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY (emp_id, emp_name);

DROP Primary Key

It is advisable to drop a primary key if it is unused.

Syntax

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

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

Lets see how to drop a primary key using the ALTER TABLE statement in Oracle

ALTER TABLE employee DROP CONSTRAINT employee_pk;

Primary key employee_pk will be dropped when above statement is executed.

ENABLE / DISABLE PRIMARY KEY

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

Syntax to DISABLE Primary key

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE employee DISABLE CONSTRAINT employee_pk;

Syntax to ENABLE Primary Key

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE employee ENABLE CONSTRAINT employee_pk;

If you find the above post useful, please put your comments below. Your suggestions and feedback are valuable to us.

Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.