Oracle Primary Key Constraint

In the Oracle database, the PRIMARY KEY constraint uniquely identifies a row. Characteristics of primary key constraint,

• Primary Key constraint doesn’t accept the null value, empty string, and duplicate values.
• It must be unique within the entire table.
• There can be only one primary key constraint in a table.

Similar to other constraints the Oracle primary key also can be applied to table level or column level. The table-level primary key constraint is also called a composite primary key.

Syntax to create primary key constraint at the column level,
columnName datatype(size) PRIMARY KEY

Syntax to create primary key in a table,
CREATE TABLE tableName (
col1 datatype(size) PRIMARY KEY,
col2 datatype(size),
……..,
);

SQL> CREATE TABLE test(
       sno NUMBER(10) PRIMARY KEY, 
       name VARCHAR2(10)
     );
Table created.

SQL> INSERT INTO test VALUES(1, 'aa');
1 row created.

SQL> INSERT INTO test VALUES(1, 'xy');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014113) violated


SQL> INSERT INTO test VALUES(NULL, 'xy');
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."SNO")

The column “sno” has a primary key constraint so, we are not allowed to insert duplicate or null values in the “sno” column. When we try to insert duplicate value then Oracle gives error:- ORA-00001: unique constraint violated, and when we try to insert null value then Oracle gives error:- ORA-01400: cannot insert NULL into (“username”.”tableName”.”columnName”)

SQL> INSERT INTO test VALUES(2, 'xy');
SQL> INSERT INTO test VALUES(3, NULL);
SQL> SELECT * FROM test;
       SNO NAME
---------- ----------
         1 aa
         2 xy
         3

There can be only one PRIMARY KEY constraint in a table. So, in the column level among all the columns of a table only one column can have the primary key. If we try to use primary key constraints on different columns then we get an error.

SQL> CREATE TABLE test1(
       sno NUMBER(10) PRIMARY KEY, 
       name VARCHAR2(10) PRIMARY KEY
     );
ERROR at line 1:
ORA-02260: table can have only one primary key

Composite Primary Key in Oracle

The table-level primary key is also called a composite primary key. It is a combination of columns as a single primary key. Here we are applying the primary key on more than one column in a table.

Syntax to create composite primary key,
CREATE TABLE tableName (
col1 datatype(size),
col2 datatype(size),
……..,
PRIMARY KEY(col1, col2, …);
);

SQL> CREATE TABLE test1(
       sno NUMBER(10), 
       name VARCHAR2(10),
       PRIMARY KEY(sno, name)
     );
SQL> INSERT INTO test1 VALUES(1, 'sophia');
SQL> INSERT INTO test1 VALUES(1, 'ameila');
SQL> INSERT INTO test1 VALUES(2, 'ameila');
SQL> INSERT INTO test1 VALUES(2, 'sophia');
SQL> SELECT * FROM test1;
       SNO NAME
---------- ----------
         1 ameila
         1 sophia
         2 ameila
         2 sophia

The PRIMARY KEY constraint is applied to the combination of sno, and name columns. We are allowed to insert data when at least one column’s value is different from all previous data. But it is not allowed to insert null for any columns.

SQL> INSERT INTO test1 VALUES(1, 'sophia');
ORA-00001: unique constraint (SCOTT.SYS_C0014114) violated

SQL> INSERT INTO test1 VALUES(NULL, 'sophia');
ORA-01400: cannot insert NULL into ("SCOTT"."TEST1"."SNO")

SQL> INSERT INTO test1 VALUES(3, NULL);
ORA-01400: cannot insert NULL into ("SCOTT"."TEST1"."NAME")

SQL> INSERT INTO test1 VALUES(NULL, NULL);
ORA-01400: cannot insert NULL into ("SCOTT"."TEST1"."SNO")

Add PRIMARY KEY to Existing Table Oracle

In all relational databases whenever we are copying a table from another table then except NOT NULL constraint all other constraints are never copied. So, we need to add constraints on the existing table or column.

We can enable the primary key on an existing column of a table or we can add a new column with primary key constraint.

Enabling PRIMARY KEY to existing columns

The syntax for column level:-
ALTER TABLE tableName
ADD PRIMARY KEY(columnName);

Condition:- In column level, that column must not have any null value or duplicate data.

The syntax for table level:-
ALTER TABLE tableName
ADD PRIMARY KEY(col1, col2, … col_N);

Condition:- In table level, combination of those columns must not contain duplicate data and none of these columns should contain any null value.

While enabling PRIMARY KEY constraint, if duplicate data is found then Oracle gives error:- ORA-02437: cannot validate – primary key violated, and if the column contains a null value then Oracle gives error:- ORA-01449: column contains NULL values; cannot alter to NOT NULL

Example:- Let’s say we have a table test2 having sno and sname columns and we want to enable PRIMARY KEY on sno column.

SQL> CREATE TABLE test2(
       sno NUMBER(10),
       name VARCHAR2(20)
     );
Table created.

Enabling PRIMARY KEY constraint on an existing column of a table at column level.

SQL> ALTER TABLE test2 
     ADD PRIMARY KEY(sno);
Table altered.

Adding PRIMARY KEY to a new column

Syntax to add a column with PRIMARY KEY constraint,
ALTER TABLE tableName
ADD columnName datatype(size) PRIMARY KEY;

Example:-

SQL> CREATE TABLE test3(
       name VARCHAR2(20)
     );
Table created.

SQL> ALTER TABLE test3 
     ADD sno NUMBER(10) PRIMARY KEY;
Table altered.

There is an alternate way, first add column and then enable the PRIMARY KEY constraint on that existing column.

Assigning User-defined Constraint name to PRIMARY KEY

In the Oracle database, whenever we are creating a constraint then oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called a pre-defined constraint name. Example:-

SQL> INSERT INTO test3 VALUES('abc', 1);
1 row created.

SQL> INSERT INTO test3 VALUES('abc', 1);
ORA-00001: unique constraint (SCOTT.SYS_C0014220) violated

Here SYS_C0014220 is the Oracle generated unique identification number of constraints applied on sno column of the test4 table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST3';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014220

Note that in the above query table name TEST3 is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here TEST3 is a record of user_cons_columns.

When Constraint is enabled at table level i.e. for Composite PRIMARY KEY the pre-defined constraint name will be same of those columns.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST1';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014114
NAME            SYS_C0014114

The pre-defined constraint name can create confusion for others and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column. We can assign a user-defined constraint name while creating the constraint.

Syntax to assign a user-defined name to constraint:-
CONSTRAINT user-definedname constriantType

Example:-

SQL> CREATE TABLE test4 (
       sno NUMBER(10) CONSTRAINT primary_sno PRIMARY KEY, 
       name VARCHAR2(15) 
     );
Table created.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST4';
COLUMN_NAME    CONSTRAINT_NAME
-------------- --------------
SNO            PRIMARY_SNO

Note:- User-defined constraint name must be unique within a user else Oracle gives error:- ORA-ORA-00955: name is already used by an existing object.

Dropping PRIMARY KEY

In Oracle database, there can be only one PRIMARY KEY constraint on a table, so dropping PRIMARY KEY constraint is very easy.

Syntax:-
ALTER TABLE tableName
DROP PRIMARY KEY

Using above syntax we can drop PRIMARY KEY constraint at column level and also at table level. Example:-

SQL> ALTER TABLE test3 
     DROP PRIMARY KEY;
Table altered.

We can also drop primary key using constraint name.

Syntax:-
ALTER TABLE tableName
DROP CONSTRAINTS constraint_name;

Example:-

SQL> ALTER TABLE test4
     DROP CONSTRAINTS PRIMARY_SNO;
Table altered.

Note:- Generally, we can’t drop PRIMARY KEY constraint along with REFERENCED KEY. To overcome this problem, the Oracle provided the CASCADE clause along with ALTER DROP, which is used to drop the primary key constraint.

Syntax:-
ALTER TABLE tableName
DROP PRIMARY KEY CASCADE;

Enable / Disable

Since there can be only one primary key in a table, so we can use below syntax,

Syntax to disable primary key,
ALTER TABLE tableName
DISABLE PRIMARY KEY;

Syntax to enable primary key,
ALTER TABLE tableName
ENABLE PRIMARY KEY;

We can also do the same using constraint name.

Syntax to disable primary key,
ALTER TABLE tableName
DISABLE CONSTRAINT constraint_name;

Syntax to enable primary key,
ALTER TABLE tableName
ENABLE CONSTRAINT constraint_name;

Note that we can enable primary key only if the applied columns contain unique values and don’t contain an empty string, null or duplicate values.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search