Oracle PRIMARY KEY Constraint with Examples

In this article, we will be learning about the Primary constraints and how to create, alter, drop or enable/disable them in Oracle.

What is a Primary Key?

The Primary Key constraint is used for uniquely identifying each row of a table. Primary constraint can be applied on one column or a combination of columns, but each table can have only one Primary Key.

A Primary Key constraint applies the following specific rules on a column:

1. Each value in primary key column must be unique and NOT NULL.
2. Even a empty string is not allowed in Primary Key column.
3. A primary key value should ideally not be changed with time.

Defining Primary Key Constraint

We can define Primary key constraint in two ways:

1. Column Level
2. Table Level

Defining Primary Key Constraint at Column Level

While defining Constraint at column level we just mention the constraint adjacent to the name of column, while creating a new table.

Syntax

CREATE TABLE [schema_name.]table_name
( Column1 datatype [CONSTRAINT constraint_name] constraint_type,
  Column2 datatype,
  Column3 datatype,
...
) ;

Here,

Schema_name – Optional. It is the name of schema in which the table belongs.

table_name – It is the name of table that we want to create.

column1, column2,.. – These are the names of columns in the table.

datatype – These are the data types of the columns that are present in table. Ex. NUMBER, VARCHAR2 etc.

CONSTRAINT – We use this keyword for applying a specific constraint on column. In this case, column1.

constraint_name – Optional. There are 2 ways of naming a constraint, i.e, User Named and System Named. Either we can give the name of constraint by ourselves or let Oracle generate a system defined unique constraint name for the constraint that we created.

constraint_type – Here, we mention the type of constraint that we are about to create. Ex. Primary Key, Foreign Key, Unique, Check, NULL etc.

Defining Primary Key Constraint at Table Level

We can define the Primary key Constraint at table level by mentioning the constraint after mentioning all columns while creating a new table.

Syntax

CREATE TABLE [schema_name.]table_name
( Column1 datatype,
  Column2 datatype,
  Column3 datatype,
...
[CONSTRAINT constraint_name] constraint_type (Column1, Column2, …),
) ;

Here,

Everything is same as above except we define the constraint at the end after mentioning all the columns.

While defining constraint at column level we can also create composite primary key by applying primary key on more than one columns. By applying Primary key on multiple columns, Oracle creates a set of values appearing in these columns and validates each set for its uniqueness.

Create Primary Key Constraint – Using CREATE TABLE Statement

Let’s create a new table for creating new Primary Key constraint.

CREATE TABLE players
(
  player_id NUMBER(10),
  player_name VARCHAR2(50),
  team_name VARCHAR2(50),
  CONSTRAINT players_pk PRIMARY KEY (player_id)
);

Here, we defined Primary Key at Table Level and named it ourselves as ‘players_pk’. The column player_id will now only accept Unique and NON-NULL values.

We can also define Primary key constraint at column level using the following query:

CREATE TABLE players
(
  player_id NUMBER(10) PRIMARY KEY,
  player_name VARCHAR2(50),
  team_name VARCHAR2(50)
);

Here, the name of PRIMARY KEY Constraint will be system generated by Oracle.

Note: The advantage of defining PRIMARY KEY constraint at ‘Table Level’ is that we can apply Primary key constraint to multiple tables at once, which is not possible when defining constraint at ‘Column Level’.

Create Primary Key Constraint – Using ALTER TABLE Statement

We can also add constraint after the table has already been created, using ALTER TABLE statement. The syntax for adding Primary Key Constraint using ALTER TABLE statement is as follows:

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

Let’s add a primary key to players table using ALTER TABLE statement:

ALTER TABLE players
ADD CONSTRAINT player_pk PRIMARY KEY (player_id );

We can add only one Primary Key to the table and for using this statement we first need to drop the previous primary key constraint. We can then use this query for adding PRIMARY KEY constraint using ALTER TABLE.

DROP Primary Key Constraint

We can use ALTER TABLE statement for dropping Primary Key Constraint, the syntax is as follows:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Let’s see one example:

ALTER TABLE players
DROP CONSTRAINT player_pk;

Here, we dropped or deleted the ‘player_pk’ constraint that we previously created in players table.

Enable/Disable Primary Key Constraint

We can enable or disable a Primary key using ALTER TABLE statement. The syntax is as follows:

ALTER TABLE table_name
[ENABLE/DISABLE] CONSTRAINT constraint_name;

Let’s have a look at the examples:

ALTER TABLE players
DISABLE CONSTRAINT player_pk;

Here, we use this query for disabling the primary key constraint that we added.

Let’s look at one more example to enable the same primary key:

ALTER TABLE players
ENABLE CONSTRAINT player_pk;

Composite Primary Key Constraint

When we add a Primary Key constraint to more than one columns at once it becomes a Composite Primary key constraint.

Let’s create a dummy table named ‘Table1’ for this example which has two columns, Col1 and Col2. For creating the table and applying primary key constraint on these two columns we use the following query:

CREATE TABLE Table1
(
  col1 NUMBER(10),
  col2 NUMBER(10),
  CONSTRAINT col_pk PRIMARY KEY (col1, col2)
);

Let’s try to insert some values in the table:

INSERT INTO Table1 VALUES ( 1, 3);
INSERT INTO Table1 VALUES ( 3, 1);
INSERT INTO Table1 VALUES ( 1, 0);

We did not face any problem while inserting each of these values as Oracle considers them as a set and each set is unique. Both the values and order of values is important in the set.

Now, lets try to insert 2 more values:

INSERT INTO Table1 VALUES ( 1, 0);
INSERT INTO Table1 VALUES ( NULL, 3);

We received an error while entering these values as first one is a duplicate set and the second one contains a NULL value which is not allowed as we have applied PRIMARY KEY constraint on both the columns.

In this article, we learned about Primary key constraints and how we can create, add, drop and enable/disable these constraints. We also learned the difference between defining constraints at column level versus defining constraints at table level.