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:

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.

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:

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:

Useful Resources

• Oracle Primary Key Constraint
• Primary Key Tips