Primary Key Constraints in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Primary Key Constraint in Oracle with Examples

In this article, I am going to discuss Primary KEY Constraint in Oracle with Examples. Please read our previous article where we discussed CHECK Constraint in Oracle with Examples. At the end of this article, you will understand everything about Oracle Primary Key Constraints with Examples.

What is Primary Key in Oracle?

The Primary Key in Oracle is the combination of UNIQUE and NOT NULL Constraint. That means it will not allow either NULL or Duplicate values into a column or columns on which the primary key constraint is applied. Using the primary key, we can enforce entity integrity i.e. using the primary key we can uniquely identify each record in a table.

In Oracle, a table can contain only 1 Primary Key which can be either on a single column or multiple columns i.e. the composite primary key. The Primary Key constraint in Oracle can be applied to numeric and string data types such as INT, VARCHAR, etc.

The Primary Key Constraint can be applied either at the column level or at the table level. As it is allowing the Primary Key to be applied at the table level, so we have the option to create a composite primary key i.e. Primary Key based on multiple columns.

Imposing PRIMARY KEY Constraint at Column Level in Oracle:

Let us understand the Primary Key Constraint with some examples. The first point about the Primary Key is, a table can have only one primary key. Let us prove this. Please try to execute the following SQL Script where we used the Primary key on more than one column (Id and Email).

CREATE TABLE Employee 
( 
    Id   INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Email  VARCHAR2(20) PRIMARY KEY,
    Department  VARCHAR2(10)
);

When we try to execute the above Create Table query, we will get the error ORA-02260: table can have only one primary key as shown in the below image. The error message clearly tells us that a table can have only one primary key.

To overcome the above error, let us remove one primary key which is applied on the Email column and execute the CREATE TABLE statement.

CREATE TABLE Employee 
( 
    Id   INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Email  VARCHAR2(20),
    Department  VARCHAR2(10)
);

Now, when we execute the above CREATE TABLE query, it is executed successfully and creates the Employee table as shown in the below image. Here, we marked the Id column as the primary key.

Adding Duplicate value in the Primary Key Column:

One of the Key features of Primary Key is that it will not allow inserting duplicate data on the column on which the Primary Key Constraint is applied. Let us prove this. Let’s execute the following insert statement to insert a record into the Employee table.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Sambit', 'Sambit@g.com', 'IT');

Once you execute the above INSERT Statement, you will see that one record is inserted into the Employee table as expected as shown in the below image.

Now let us see what happens when we try to insert a duplicate value into the Primary key column i.e. into the Id column. In our Employee table, one record exists with Id 1. Now. let us try to insert another employee with the same ID 1. Please try to execute the following INSERT statement to insert a duplicate record into the Employee table.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Anurag', 'Anurag@g.com', 'IT');

When we try to execute the above INSERT SQL statement, it will give us the error ORA-00001: unique constraint (SYSTEM.SYS_C007656) violated as shown in the below image. That means the Primary Key constraint in Oracle or any other databases will not accept duplicate values in it.

Inserting NULL in Primary Key Column in Oracle:

Now, let us try to insert a NULL value into the primary key column i.e. Id column by executing the below insert statement.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (NULL, 'Anurag', 'Anurag@g.com', 'IT');

When you try to execute the above INSERT statement, it will give us the error ORA-01400: cannot insert NULL into (“SYSTEM”.”EMPLOYEE”.”ID”) as shown in the below image which clearly states that the Id value cannot be the null value which proves that Primary Key will not accept NULL.

Note: So, the Primary key neither accepts NULL Values or Duplicate Values in Oracle.

As we already discussed except the NOT NULL constraint, all other constraints can be imposed either at the Table or Column level. Let us understand the same concept with PRIMARY KEY Constraint.

Imposing PRIMARY KEY Constraint at Table Level in Oracle:

Let us understand how to impose PRIMARY KEY constraint at table level in Oracle with an example. As you can see in the below Create Table statement, first, we created all the three columns and then we created the PRIMARY KEY constraints and while creating the PRIMARY KEY constraint we specified the column name to which the constraint belongs.

CREATE TABLE Student
( 
    Id      INT, 
    Name    VARCHAR2(20), 
    Email   VARCHAR2(20),
    PRIMARY KEY(Id)
);

When you execute the above SQL Script, then you will see that the Student table is created as shown in the below image with Id column as the Primary key column.

Note: There is no difference in behavior whether we imposed the PRIMARY KEY Constraint at the table level or at the column level but if we imposed the Primary Key Constraint at the table level, then we have the chance to create a composite PRIMARY KEY i.e. Primary Key based on multiple columns.

Creating PRIMARY KEY with Custom Constraint name in Oracle:

Let us understand how to provide a custom name to the PRIMARY KEY constraint in Oracle. In order to understand this, please execute the below SQL Script which will create the StudentDetails table. If you further notice, here we created the PRIMARY KEY constraints by giving our own names such as Student_Id_Primary.

CREATE TABLE StudentDetails
( 
    Id      INT, 
    NAME    VARCHAR2(20), 
    Email   VARCHAR2(30),
    CONSTRAINT Student_Id_Primary PRIMARY KEY (Id)
);

When you execute the above SQL Script, then you will see that the StudentDetails table is created as shown in the below image.

Composite Primary key in Oracle?

It is also possible in Oracle to create the Primary Key constraint on more than one column and when we do so, it is called a Composite Primary Key. It is only possible to impose the Composite Primary Key constraint at the table level, it is not possible at the column level. In a composite primary key in Oracle, each column can accept duplicate values but the combination should not be duplicated. If this is not clear at the moment, then don’t worry, we will try to understand this with some examples.

Examples to Understand Composite Primary Key in Oracle:

Please have a look at the following create table statement. Here, we impose the Primary Key Constraint at the table level i.e. after all the columns are created. While creating the primary key constraint we are providing two columns i.e. Name and Email. As the Primary Key is created based on more than one column, it is called Composite Primary Key.

CREATE TABLE EmployeeDetails 
( 
    Id  INT, 
    Name VARCHAR2(20), 
    Email  VARCHAR2(20), 
    PRIMARY KEY(Name, Email) 
);

Once you execute the above SQL Statement, you will see that EmployeeDetails was created as expected as shown in the below image.

Now, execute the below INSERT SQL Statements.

INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (1, 'Sambit', 'Sambit@g.com');
INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (2, 'Sambit', 'Sambit1@g.com');
INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (3, 'Sambit1', 'Sambit1@g.com');

Once you execute the above INSERT Statements, then you will see that three records are inserted into the EmployeeDetails table as shown in the below image. Here, if you notice, in the second and third INSERT Statements, we have some duplicate values. In Statement2, the Name is duplicated and in Statement3, the Email is duplicated. But all these records are inserted, this is because the combination of Name and Email is not duplicated.

Now, try to execute the following INSERT Statement.

INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (4, 'Sambit', 'Sambit@g.com');

When you try to execute the above INSERT Statement, you will get the error ORA-00001: unique constraint (SYSTEM.SYS_C007659) violated as shown in the below image. This is because the combination of the above Name and Email is already existing in the database.

How to add PRIMARY KEY Constraints to the existing table in Oracle?

It is also possible in Oracle to add PRIMARY KEY Constraints to the existing table. But the condition is, the table should not have any primary key and on the column(s) on which we are going to apply the Primary Key Constraint should not contain any NULL or Duplicate Values else it will not allow you to create the Primary Key Constraint. Let us understand this with an example. First, create the following table without using the Primary Key Constraint.

CREATE TABLE Test
( 
    Id      INT, 
    NAME    VARCHAR2(20), 
    Email VARCHAR2(20)
);

Once you created the above Test table, now we want to apply the Primary Key Constraint on the Id column. To do so, please execute the following alter table statement.

ALTER TABLE Test ADD PRIMARY KEY (Id);

Once you execute the above ALTER Statement, it will create the PRIMARY KEY Constraint on the ID column.

How to DELETE PRIMARY Constraints in Oracle?

You can use the following SQL Statement to delete an existing PRIMARY KEY Constraint in Oracle. The following SQL Statement will delete the PRIMARY Constraint from the Id column of the Test table.
ALTER TABLE Test DROP PRIMARY KEY;

Once you execute the above ALTER Statement, it will delete the PRIMARY KEY Constraint from the Test table. 

When do we need to choose the Primary Key Constraint?

When we need the following features on a column, then we need to make that column as the Primary Key in Oracle.

1. NULLs should not be allowed.
2. It should be unique
3. It should not be modified.

What are the differences between the primary key and the unique key in Oracle?

As we already discussed both unique key and Primary Key handles the uniqueness of a column on which they are applied. But the difference is that by default in Oracle the primary key creates a unique clustered index whereas the unique key creates a unique non-clustered index on the column. Another difference between them is that the primary key column doesn’t allow NULL values whereas the unique key column allows NULL values.

Points to Remember

1. The PRIMARY KEY Constraint is useful to restrict storing of duplicate data row values in a given column.
2. The PRIMARY KEY is similar to UNIQUE Constraint but unlike UNIQUE Constraint there can be only one PRIMARY KEY for one table.
3. The PRIMARY KEY automatically sets the UNIQUE Constraint for that table column.
4. The PRIMARY KEY column can not contain NULL values.
5. The Primary key can be defined while creating a new database table or can be added by using ALTER TABLE statement.

The Primary Key Constraint in Oracle is also used to make a relationship with a Foreign Key constraint on another table. So, in the next article, I am going to discuss Foreign Key Constraint in Oracle with examples. Here, in this article, I try to explain the Primary Key Constraint in Oracle with Examples. I hope you enjoy this Primary Key in Oracle with Examples article.