How to Define Primary Key Constraint

Whenever we talk about database constraint the first thing which comes into our mind is the primary key constraint. So today in this tutorial we will see what is a Primary key constraint and the different ways of defining it on a table.

Primary key constraint is an Input/output Data constraint which serves the purpose of uniquely identifying the rows in a table.

What is a Primary Key Constraint?

Primary key constraint is the combination of NOT NULL and UNIQUE constraints. In a more proper manner you can define a primary key constraint as an Input/output Data constraint which serves the purpose of uniquely identifying the rows in a table.

Types of Primary Keys

There are two types of Primary keys:

  1. Simple Primary Key and
  2. Composite Primary Key.

Simple Primary Key

A primary key constraint that is defined using only one column of a table in the database is called Simple Primary Key Constraint.

Composite Primary Key

A primary key constraint that is defined using more than one column of a table in the database is called Composite Primary key constraint.

Primary key has a single function of identifying a unique row in the table. In case the simple primary key fails to identify that unique row, the user must then define a composite primary key. There are some restrictions on composite primary key, for example a composite primary key can be defined using up to 32 columns of a table in oracle database.

Quick Tip
How to ensure the uniqueness of a primary key constraint? 
Oracle automatically creates a unique index so that the requirement of the uniqueness of the PRIMARY KEY constraint is fulfilled.

How to Create a Primary Key Constraint in Oracle Database

Primary key constraint can be defined in two ways:

  1. Using CREATE TABLE statement and
  2. Using ALTER TABLE statement

You can define a primary key constraint either during the creation of a table using CREATE TABLE statement or after creating a table using ALTER TABLE statement.

If you choose to define a primary key using CREATE TABLE statement then you have two different levels at which you have to define a primary key.

  1. At Column Level
  2. At Table Level.

sql primary key by manish sharmaLet’s learn all about the above mentioned ways of defining a Primary Key constraint one by one.

Defining Primary Key Using CREATE TABLE

As mentioned above we can define a primary key in two ways using create table. Let’s start with the first way which is Defining Primary key at Column Level.

Define Primary key at Column Level

In the create table statement you define any column as a primary key column just by putting reserved phrase “Primary key” right after defining the column. This means you have to put reserved phrase “Primary Key” after data type and size in column definition. For example

Example 1: Primary Key at Column Level

CREATE TABLE product_master
  Product_id  NUMBER(3)  PRIMARY KEY,
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5)

As you can see in the above code the column product_id is a primary key column. But it is always a good practice to give a unique and meaningful name to your primary key constraint every time you create it. This will make the managing of your constraint much easier.

How to Name Your Constraint in Oracle Database?

In case you do not provide a meaningful name to your constraint then the oracle database server gives it a default name automatically. If you have several constraints on your table then using this default name makes it very difficult to find a specific constraint.

You can use keyword “CONSTRAINT” to give your constraint a meaningful name. Let’s modify the above code and give our constraint a name.

Example 2: How to name a primary key constraint

CREATE TABLE product_master
  Product_id   NUMBER(3)   CONSTRAINT   promstr_col1_pid_pk   PRIMARY KEY,
  Product_name   VARCHAR2(30),
  Product_price   NUMBER(5)

That is how we define a primary key constraint at column level using Create Table Statement in Oracle database. Now let’s see the second way of creating a primary key.

Define Primary key at Table Level

Defining the primary key constraint at table level is one of my favorite ways as it helps me to manage all my constraints specially when dealing with a huge line of code.

Defining a constraint at table level separates the column definition from the constraint definition. In this way you first define all the columns of a table and then you define all your constraints in the Create Table Statement. For Example

Example 3: Primary Key Constraint at Table Level

Let’s again take the above example and see how we can define the primary key constraint promstr_col1_pid_pk at table level:

CREATE TABLE product_master
  Product_id  NUMBER(3), 
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5),
  CONSTRAINT promstr_col1_pid_pk PRIMARY KEY (product_id)

Please watch the video tutorial on the same topic for line by line explanation of the above code.

How to Define Primary Key Using ALTER TABLE statement?

You can use ALTER TABLE statement to add the constraint in an already created table or to change the definition of already defined constraint in the table.

Example 4:

Let’s say we have a table “Customer” with 3 columns Cust_id, cust_name, phone_no and we don’t have any Primary Key constraint on any column and now we want to add Primary Key constraint on cust_id column. To do this we can use ALTER TABLE statement:

ALTER  TABLE  customer  ADD  CONSTRAINT  cust_cid_pk  PRIMARY  KEY  (cust_id);

That is how we use ALTER TABLE statement to add a primary key constraint in an already created table.

How to Define a Composite Primary Key Constraint?

As said above that the primary key defined using more than one column is called a composite primary key. Let’s see how to define a composite primary key.

Example 5: Composite Primary Key

  cust_id NUMBER(3),
  cust_name VARCHAR2(3),
  phone_no NUMBER(10),
  CONSTRAINT cust_cid_pk PRIMARY KEY ( cust_id, phone_no)

In the above code we create a table with the name ‘Customer’ which has 3 columns. The primary key constraint is defined using two columns cust_id and phone_no.

Example 6: Composite Primary Key Using ALTER TABLE

Similar to simple primary key you can add a composite primary key to an already created table using ALTER TABLE statement.

ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id,phone_number);

How To Enable and Disable a Primary Key Constraint?

If you were wondering what is the requirement of giving a name to a constraint then here is the answer. Modifying a constraint becomes easier if your constraint has a unique and meaningful name. If you do not provide a name to your constraint then oracle server gives a default name to it which is quite generic and it becomes difficult to find a specific constraint using that name.

So let’s modify the primary key constraint and see how to enable or disable it using the name of the constraint.

Example 7: Enable or Disable primary key

For example let’s say you want to disable cust_cid_pk constraint which we earlier defined on the Customer table.

Disable Primary key constraint


If you want to enable this constraint then simply write

Enable Primary key constraint


How to Check Constraint on a Table

As we know that DESC statement shows nothing about constraints on a table. But Oracle Database provides us several DATA DICTIONARIES for checking or describing all the constraints which we have defined on our table.

These Data Dictionaries are:


Here USER_CONSTRAINTS gives a brief about the constraint on a table. USER_CONS_COLUMNS is a Data Dictionary which holds detailed information about columns of a table. Let’s see how to use them to get information about the primary key constraint.

For example let’s say you want to see constraint details on customer table




 FROM user_cons_columns WHERE table_name = ‘CUSTOMER’;

Restrictions on a primary key constraint

  1. You cannot delete a Primary key if it is referenced by a foreign key in some other table.
  2. A table can have only One Primary key no matter whether it’s Simple Primary Key Or Composite Primary Key.
  3. Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.
  4. As primary key is all about row or record’s uniqueness thus it will not allow duplicate values.
  5. When a Primary Key constraint has been defined on multiple columns then its values can be duplicated provided the duplication is happening within one single column. However the combination of values of all the columns defining each primary key constraint should be unique.
  6. Data-types such as LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type are not allowed with the columns which are part of Primary key. Any attempt of creating a primary key with the column of these data-types will raise SQL Error: ORA-02269.
  7. The size of the primary key cannot exceed approximately one database block.
  8. As I have already mentioned above that a composite primary key can have maximum of 32 columns.
  9. The Primary Key and Unique Key should never be designated as the same column or combination of columns.
  10. You cannot specify a primary key when creating a sub view in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.
  11. Unique cluster Index gets created automatically at the time of creating Primary key.
  12. Although it is not necessary for you to define a primary key yet it is always recommended to do so.

So that’s all about PRIMARY KEY. Hope it gives you a detailed insight into the concept. You can visit my YouTube channel for tutorials on Primary Key. Do like and Subscribe to my videos. Thanks for reading & have a great day!