We will start with an introduction to the primary key in oracle and then we will dive into various queries on it.
Introduction to Primary Key
A primary key is a column or set of columns in the table which uniquely identifies a row in the table.
Properties of Primary key
- You cannot have duplicate values in it. i.e it should be unique in the table
- It can not be null or contain empty strings
- It should not be changed over the time
- We can have only one primary key in the table
Recommendation
- It is suggested to have numeric values as the primary key as it is faster
- All the tables should have primary keys
How to Add primary key in oracle
The primary key can be added at the table creation or can be created after the table creation.
Let’s first check out for table creation
Primary Key at Table Creation
It can be defined at the column level or table level. Composite Primary keys are defined only at the table level. When the oracle creates the primary key, it creates the unique index on that column in the table to enforce the primary key constraints.
Let’s check out first the Column level
Column Level
SQL> CREATE TABLE DEPT_MASTER (
dept_nr NUMBER PRIMARY KEY,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date
2 );
Table created.
SQL> desc DEPT_MASTER
Name Null? Type
DEPT_NR NOT NULL NUMBER
DEPT_NAME NOT NULL VARCHAR2(100)
DEPT_STATUS NOT NULL NUMBER(1)
CREATED_AT DATE
SQL> select index_name from dba_indexes where table_name='DEPT_MASTER';
INDEX_NAME
**********
SYS_C0013850522
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER';
CONSTRAINT_NAME INDEX_NAME CONSTRAINT_TYPE
SYS_C00478605 C
SYS_C00478606 C
SYS_C00478607 SYS_C00478607 P
Now let’s see for Table level
Table Level
SQL> CREATE TABLE DEPT_MASTER (
dept_nr NUMBER ,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date,
PRIMARY KEY ("DEPT_NR")
); 2 3 4 5 6 7
Table created.
SQL> select index_name from dba_indexes where table_name='DEPT_MASTER';
INDEX_NAME
SYS_C0013850525
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER';
CONSTRAINT_NAME INDEX_NAME CONSTRAINT_TYPE
SYS_C00478605 C
SYS_C00478606 C
SYS_C00478607 SYS_C00478607 P
We can give the custom name of the primary key constraint also using add constraint clause as shown below
SQL> CREATE TABLE DEPT_MASTER (
dept_nr NUMBER ,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date,
CONSTRAINT PK_DEPT_NR PRIMARY KEY ("DEPT_NR") );
Table created.
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER';
CONSTRAINT_NAME INDEX_NAME CONSTRAINT_TYPE
SYS_C00478609 C
SYS_C00478608 C
PK_DEPT_NR PK_DEPT_NR P
Table with Primary can be represented as diagram
Alter Table Add Primary Key
Let’s see how to add the Primary after table creation
CREATE TABLE DEPT_MASTER ( dept_nr NUMBER , dept_name varchar2(100) NOT NULL, dept_status NUMBER(1,0) NOT NULL, created_at date ); SQL> alter table DEPT_MASTER add primary key ( dept_nr); Table altered. SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER'; CONSTRAINT_NAME INDEX_NAME CONST SYS_C00485778 C SYS_C00485779 C SYS_C00485780 SYS_C00485780 P
We can give custom names also while adding the primary key
SQL> alter table DEPT_MASTER add constraint DEPT_MASTER_ID primary key ( dept_nr); Table altered. SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER'; CONSTRAINT_NAME INDEX_NAME CONST SYS_C00485778 C SYS_C00485779 C DEPT_MASTER_ID DEPT_MASTER_ID P
how to create a composite primary key in oracle
Now let’s check out how to add a primary key for the composite key
CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER(6,0),
NAME VARCHAR (20) NOT NULL,
AGE NUMBER(6,0) NOT NULL,
ADDRESS VARCHAR2(25),
SALARY NUMBER(6,0),
PRIMARY KEY (CUSTOMER_ID, NAME)
);
SQL> col CONSTRAINT_NAME format a20
SQL> col INDEX_NAME format a20
SQL> col CONSTRAINT_TYPE format a5
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME INDEX_NAME CONST
SYS_C00485772 C
SYS_C00485773 C
SYS_C00485774 SYS_C00485774 P
We can give the custom name for the primary key constraints on the Composite key also
SQL>CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER(6,0),
NAME VARCHAR (20) NOT NULL,
AGE NUMBER(6,0) NOT NULL,
ADDRESS VARCHAR2(25), SQL>
SALARY NUMBER(6,0),
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID, NAME)
);
Table created.
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME INDEX_NAME CONST
SYS_C00485776 C
SYS_C00485775 C
PK_CUSTOMER PK_CUSTOMER P
The composite Primary can be represented as
how to drop primary key in oracle
We can drop the primary key using the below command. We can use the drop primary key or drop constraints
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER'; CONSTRAINT_NAME INDEX_NAME CONST SYS_C00485778 C SYS_C00485779 C DEPT_MASTER_ID DEPT_MASTER_ID P SQL> alter table DEPT_MASTER drop primary key; Table altered. SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER'; CONSTRAINT_NAME INDEX_NAME CONST SYS_C00485778 C SYS_C00485779 C SQL> alter table DEPT_MASTER add constraint DEPT_MASTER_ID primary key ( dept_nr); Table altered. SQL> alter table DEPT_MASTER drop constraint DEPT_MASTER_ID; Table altered. SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER'; CONSTRAINT_NAME INDEX_NAME CONST SYS_C00485778 C SYS_C00485779 C
How to Enable/Disable Primary Key constraints
SQL> alter table DEPT_MASTER enable primary key; Table altered. SQL> alter table DEPT_MASTER disable primary key; Table altered. SQL> alter table DEPT_MASTER disable constraint DEPT_MASTER_ID; Table altered. SQL> alter table DEPT_MASTER enable constraint DEPT_MASTER_ID; Table altered
How to Add Primary Key using Index
When the oracle creates the primary key, it creates the unique index on that column in the table to enforce the primary key constraints. But if the table has an index before the addition of the Primary key, Oracle can use that index for primary key constraints also. Oracle can enforce primary key constraints on both unique, non-unique, and composite indexes. Oracle will use the index depending on the indexes table already has. We can also use using index clause at primary key creation time also if we want to enforce constraints using a particular index
SQL> create index DEPT_MASTER_IDX on DEPT_MASTER(dept_nr); Index created. SQL> alter table DEPT_MASTER add constraint DEPT_MASTER_ID primary key ( dept_nr) using index DEPT_MASTER_IDX; Table altered. SQL> col CONSTRAINT_NAME format a20 SQL> col INDEX_NAME format a20 SQL> col CONSTRAINT_TYPE format a5 SQL> / CONSTRAINT_NAME INDEX_NAME CONST DEPT_MASTER_ID DEPT_MASTER_IDX P SYS_C00485779 C SYS_C00485778 C
Even if we dont use using index in this earlier statement, the oracle will still have the same non-unique index for enforcing primary key constraints
SQL> create index DEPT_MASTER_IDX on DEPT_MASTER(dept_nr); Index created. SQL> alter table DEPT_MASTER add constraint DEPT_MASTER_ID primary key ( dept_nr); Table altered. SQL> col CONSTRAINT_NAME format a20 SQL> col INDEX_NAME format a20 SQL> col CONSTRAINT_TYPE format a5 SQL> / CONSTRAINT_NAME INDEX_NAME CONST DEPT_MASTER_ID DEPT_MASTER_IDX P SYS_C00485779 C SYS_C00485778
how to modify primary key in oracle
We cannot just modify the Primary key value. we will need to drop the old key and create the new primary. If we have foreign key constraints referencing those. then we need to drop them first and drop the primary key and create again the new primary key
how to auto increment primary key in oracle
With 12c, we have two easy ways to implement auto increment for primary key
Identity Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is an American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example: create table test ( id number generated as identity PRIMARY KEY, name varchar2(100), email varchar2(100), password varchar2(100)firstname varchar2(100)lastname varchar2(100) );
Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition. It is a sort of auto-increment feature for a column in oracle just like MySQL
Example: create sequence tech_test_seq start with 1 increment by 1 nocycle; create table test ( id number default tech_test_seq.nextval primary key name varchar(30) );
how to rename primary key in oracle
We can easily rename the primary key constraints using alter table rename sql. This does not impact the processing and the foreign key
CREATE TABLE DEPT_MASTER ( dept_nr NUMBER , dept_name varchar2(100) NOT NULL, dept_status NUMBER(1,0) NOT NULL, created_at date ); alter table DEPT_MASTER add constraint DEPT_MASTER_ID primary key ( dept_nr); Table altered. select CONSTRAINT_NAME from user_constraints where TABLE_NAME='DEPT_MASTER' and constraint_type = 'P'; CONSTRAINT_NAME ------------- DEPT_MASTER_ID ALTER TABLE DEPT_MASTER RENAME CONSTRAINT DEPT_MASTER_ID TO DEPT_MASTER_ID_PK; select CONSTRAINT_NAME from user_constraints where TABLE_NAME='DEPT_MASTER' and constraint_type = 'P'; CONSTRAINT_NAME ------------- DEPT_MASTER_ID_PK I hope you like the content on primary key and it clear all doubts about the Primary Key concept. Please do provide the feedback and what else can be added in this post
Also Reads
Check Constraint in Oracle : Oracle Check Constraint is used to enforce integrity rules based on logical expressions, such as comparisons. The check condition must return true or false
Not Null constraint in Oracle:
NVL2 function in Oracle: Learn how to use the NVL2 function in Oracle with examples
alter table modify column oracle
oracle drop table if exists: Check how to check if table exists and then drop the table in oracle
https://en.wikipedia.org/wiki/Primary_key
Recommended Courses
Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning