Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

PRIMARY KEY Constraint in Oracle

Leave a Comment / SQL, CONSTRAINTS / By

PRIMARY KEY Constraint in Oracle

  • The PRIMARY KEY enforces the uniqueness in table.
  • PRIMARY KEY can be created on a single column or multiple columns (composite key)
  • A table can have only one PRIMARY KEY
  • PRIMARY KEY can not have a null value

Types of PRIMARY KEY Constraints:

  • SINGLE COLUMN PRIMARY KEY
  • COMPOSITE PRIMARY KEY

SINGLE COLUMN PRIMARY KEY:

In this type of constraint the primary key is defined on a single column.
We can define this type of constraint both at column level or table level.

COLUMN LEVEL DEFINITION:
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
PRIMARY KEY Constraint in Oracle: output
PRIMARY KEY Constraint in Oracle: output
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_PK_TAB';
PRIMARY KEY Constraint in Oracle: output
TABLE LEVEL DEFINITION:
DROP TABLE EMP_PK_TAB;
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6),
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
PRIMARY KEY Constraint in Oracle: output
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_PK_TAB';
PRIMARY KEY Constraint in Oracle: output

COMPOSITE PRIMARY KEY CONSTRAINT:

In this type of constraint the primary key is defined on a multiple columns.
We can define this type of constraint only at table level.

DROP TABLE EMP_PK_TAB;
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6),
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
PRIMARY KEY Constraint in Oracle: output
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_PK_TAB';

Types of PRIMARY KEY constraint definition:

  • In-line definition
  • Out-of-line definition

In-line definition:

It states that PRIMARY KEY constraint is defined during table creation, either at column level or at table level.

SINGLE COLUMN PRIMARY KEY DEFINITION:
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2)
);
COMPOSITE PRIMARY KEY DEFINITION:
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6),
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME)
);

Out-of-line definition:

It states that PRIMARY KEY constraint is defined with ALTER TABLE statement.

SINGLE COLUMN PRIMARY KEY DEFINITION:
DROP TABLE EMP_PK_TAB;
CREATE TABLE EMP_PK_TAB
(EMPLOYEE_ID     NUMBER(6),
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2)
);
USING MODIFY STATEMENT:
ALTER TABLE EMP_PK_TAB MODIFY(EMPLOYEE_ID NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
USING ADD CONSTRAINT STATEMENT:
ALTER TABLE EMP_PK_TAB DROP CONSTRAINT EMP_PK;
ALTER TABLE EMP_PK_TAB ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
COMPOSITE PRIMARY KEY DEFINITION:
ALTER TABLE EMP_PK_TAB DROP CONSTRAINT EMP_PK;
ALTER TABLE EMP_PK_TAB ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_PK_TAB';

Disabling PRIMARY KEY Constraint:

ALTER TABLE EMP_PK_TAB DISABLE CONSTRAINT EMP_PK;
SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';

Enabling PRIMARY KEY Constraint:

ALTER TABLE EMP_PK_TAB ENABLE CONSTRAINT EMP_PK;
SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';

DROP PRIMARY KEY Constraint:

ALTER TABLE EMP_PK_TAB DROP CONSTRAINT EMP_PK;
SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_PK_TAB';

Impact of PRIMARY KEY Constraint on DML operations:

ALTER TABLE EMP_PK_TAB ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID);

SINGLE COLUMN PRIMARY KEY:

INSERT:

Trying to insert null value as primary key:

INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(NULL,
       'John',
       'Smith',
       'IT_PROG',
       1000
      );
PRIMARY KEY Constraint in Oracle: error output
INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(100,
       'John',
       'Smith',
       'IT_PROG',
       1000
      );

Trying to insert duplicate value as primary key:

INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(100,
       'John',
       'Smith',
       'IT_PROG',
       1000
      );
PRIMARY KEY Constraint in Oracle: error output
UPDATE:

Updating primary key as null value:

SELECT * FROM EMP_PK_TAB;
UPDATE EMP_PK_TAB
SET EMPLOYEE_ID = NULL
WHERE EMPLOYEE_ID = 100;

Inserting another record:

INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(200,
       'Steve',
       'Smith',
       'IT_PROG',
       2000
      );
SELECT * FROM EMP_PK_TAB;

Updating primary key with duplicate value:

UPDATE EMP_PK_TAB
SET EMPLOYEE_ID = 100
WHERE EMPLOYEE_ID = 200;
PRIMARY KEY Constraint in Oracle: error output

COMPOSITE PRIMARY KEY:

DELETE EMP_PK_TAB;
ALTER TABLE EMP_PK_TAB DROP CONSTRAINT EMP_PK;
ALTER TABLE EMP_PK_TAB ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME);
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_PK_TAB';
INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(100,
       'Jhon',
       'Smith',
       'IT_PROG',
       1000
      );
INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(100,
       'Steve',
       'Smith',
       'IT_PROG',
       1000
      );
SELECT * FROM EMP_PK_TAB;

We can see that we have entered 100 twice since the PRIMARY KEY is the composite key.
But oracle will not allow NULL value in any of the composite key columns.

Inserting NULL as EMPLOYEE_ID:

INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(NULL,
       'Steve',
       'Smith',
       'IT_PROG',
       1000
      );
PRIMARY KEY Constraint in Oracle: error output

Inserting NULL as FIRST_NAME:

INSERT INTO EMP_PK_TAB(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
VALUES(100,
       NULL,
       'Smith',
       'IT_PROG',
       1000
      );
PRIMARY KEY Constraint in Oracle: error output

So composite PRIMARY KEY is not allowing any of the key as NULL.

So we can see that PRIMARY KEY is neither allowing NULL values nor duplicate values.

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.