Home » Oracle Database » Create Table With Primary Key in Oracle

Create Table With Primary Key in Oracle

A primary key in Oracle is a single field or a set of fields that uniquely identifies a record. A null value cannot exist in any of the fields that make up the primary key. There can only be one primary key in a table. In this tutorial, you will learn how to create a table with the primary key in Oracle.

There are three different methods to create the table with the primary key in Oracle. Examples and the explanation are given below:

1

Create Table with Single Primary Key Column in Oracle

If there is a need to create a table in Oracle with only one column as the primary key then you can use the below example:

Create Table Customer (
   customer_id number PRIMARY KEY,
   customer_name varchar2(100),
   address varchar2(200)
   )
/

The table customer will be created with the customer_id as the primary key column. To create a table with multiple primary key columns, check the below example.

2

Create a Table With Multiple Primary Key Columns in Oracle

You can have the multiple primary key columns in a table in Oracle using the constraint clause. The following is an example:

Create Table Customer (
   customer_id number,
   account_id number,
   customer_name varchar2(100),
   address varchar2(200),
   constraint pk_customer PRIMARY KEY (customer_id, account_id))
/

Now the customer table will have two primary key columns customer_id and account_id.

You can also add the primary key constraint later after creating the table in Oracle. In this case, it could be one column or more. Below is an example:

3

Adding Primary Key Constraint Using ALTER TABLE Command

To define the primary key columns after creating the table use the ALTER TABLE command. First, we will create a table with no primary key:

Create Table Customer (
   customer_id number,
   account_id number,
   customer_name varchar2(100),
   address varchar2(200)
)
/

Now add the primary key using the ALTER TABLE statement in Oracle:

Alter Table Customer add
   constraint pk_customer PRIMARY KEY (customer_id, account_id);

This will create the primary key constraint for the customer_id and account_id columns.

Read also: Alter Table Command in Oracle.

Video Tutorial

You can see the below video tutorial on how to create a primary key in Oracle:

Creating Primary Key Constraints

Useful Resources

What is a Primary Key? - Definition from Techopedia - Techopedia

August 14, 2020 - Techopedia

What is a Primary Key? - Definition from Techopedia  Techopedia...

Modifying a Primary Key Index in Oracle 11.2.0.x - Database Journal

May 8, 2014 - Database Journal

Modifying a Primary Key Index in Oracle 11.2.0.x  Database Journal...

DBMS keys: Primary, super, foreign and candidate keys with examples - TechTarget

December 16, 2020 - TechTarget

DBMS keys: Primary, super, foreign and candidate keys with examples  TechTarget...