Oracle add primary key

In this Oracle tutorial, we will learn how to add the primary key in oracle 19c and 21c. Also, we will cover the following topics in this tutorial.

• Oracle how to add primary key
• Oracle add primary key syntax
• Oracle how to add column to primary key
• Oracle how to change primary key
• Oracle add primary key using index
• Oracle add primary key sequence
• Oracle add primary key to existing table with data
• Oracle add constraint primary key novalidate
• Oracle add composite primary key
• Oracle add primary key constraint multiple columns
• Oracle add primary key to materialized view

Oracle how to add primary key

A primary key in Oracle identifies each row uniquely in the table. There are two major properties of primary key:

• Primary key can’t be empty
• Primary key must be unique

While fetching information from the table, an algorithm searches each row and column to find the provided input from the table which is an inefficient way as it takes time and resouces.

The primary key identifies each row uniquely when it is included in the search query then the algorithm knows in which row it has to look for. This way it takes less time and resources.

Oracle add primary key syntax

In this section, we will see the syntax for adding the primary key in the oracle table. Primary key can be added in two ways:-

• Add Primary Key Inline
• Add Primary Key using Constraint

Add Primary Key inline :-

While creating a table, mention the primary key constraint infront of the column that you want to create as a primary key column. Professionals prefer to use other options but this method is great for those who are learning.

Below is the example to add a primary key in a table using an inline method in oracle databases 19c and 21c.

-- Inline 
CREATE TABLE employee(
    emp_id INTEGER PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_sal FLOAT);

Add Primary Key using Constraint :-

This method is most preferable by the professionals due to the feature of creating a name for this primary key PK_EMP_TABLE in this case.

-- Using constraint
CREATE TABLE employee(
    emp_id INTEGER,
    emp_name VARCHAR2(50),
    emp_sal FLOAT,
    CONSTRAINT PK_EMP_TABLE PRIMARY KEY (COLN_1));

Oracle how to add column to primary key example

In this section, we have created a table CountryEvents, where we have displayed names of festivals, events, and their origin country name. We have created fest_id as the primary key in a table.

-- CREATE TABLE
CREATE TABLE COUNTRY_EVENTS(
    FEST_ID INTEGER,
    FEST_NAME VARCHAR2(100),
    ORIGIN_COUNTRY VARCHAR2(100),
    Constraint PK_CO_EVE PRIMARY KEY (FEST_ID)


);

-- INSERT DATA 
INSERT INTO COUNTRY_EVENTS 
VALUES(101, 'New Orleans Mardi Gras', 'UNITED STATES OF AMERICA');

INSERT INTO COUNTRY_EVENTS 
VALUES(102, ' Aloha Festival', 'UNITED STATES OF AMERICA');

INSERT INTO COUNTRY_EVENTS
VALUES(103, 'Hogmanay: New Year In Scotland', 'UNITED KINGDOM');

INSERT INTO COUNTRY_EVENTS
VALUES(104, ' Oktoberfest', 'GERMANY');

INSERT INTO COUNTRY_EVENTS
VALUES(105, 'FANTASY FEST', 'UNITED KINGDOM');

INSERT INTO COUNTRY_EVENTS
VALUES(106, 'Jorvik Viking Festival', 'GERMANY');


-- COMMIT 
COMMIT;

-- VIEW DATA 
SELECT * FROM COUNTRY_EVENTS;

Output:

In the below output, a table is created with FEST_ID as a primary key.

Also, check: How to create table in Oracle

Oracle how to change primary key

The primary key shouldn’t be changed in the oracle database because if it changes in one table then it has to be changed in other tables as well.

It is recommended to add a primary key on a column that doesn’t have any meaning so that it can be used only for identifying a row in a table.

• Suppose there is a requirement of adding a new column to an existing table and now we have to shift the primary key from previous column to the new column.
• Before changing take a note of all the columns in which current primary key is used as a foreign key.
• In oracle database 19c & 21c using Alter Table command we can change primary key in the table.
• In the previous section – Oracle how to add column to primary key example we have created a table and assigned a primary key constraint with the name PK_CO_EVE. We will use the same constraint name in our below examples.

Example:

In this example, first, we have shared the script using which you can remove the primary key constraint from the table. Here PK_CO_EVE is the name of the primary key constraint that we created in the previous section.

ALTER TABLE COUNTRY_EVENTS 
DROP CONSTRAINT PK_CO_EVE

The next script shows how to add more columns as the primary key in the oracle database. You can use this code to change the primary key as well. We have used a few imaginary columns (Fid, country_id) on which we are going to add a primary key constraint.

ALTER TABLE COUNTRY_EVENTS
ADD CONSTRAINT PK_CO_EVE PRIMARY KEY (FID, COUNTRY_ID)

Read: Alter Table Add Column Oracle

Oracle add primary key using index

Every time a primary key constraint is added to the column, an index is automatically created in oracle database 19c. One primary key can have multiple indexes but only one can be set to visible at a time.

Use the below command to view the index name and visibility on the primary key in the oracle database. Instead of Players put in the name of your table name.

SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE TABLE_NAME='PLAYERS';

More indexes can be created for the same primary key table but it is important to set the previous index to invisible. Use Alter command to change the visibility of the primary key column. In the below code, PK_PLAYERS is the index name.

ALTER INDEX PK_PLAYERS INVISIBLE;

Use CREATE INDEX statement in oracle to create a new index for the specified column. The statement requires the index and column name of the table. In the below code, NEW_INDEX is the name of the index and Id is the name of the primary key column.

CREATE INDEX NEW_INDEX ON PLAYERS(ID);

Example:

In the below example, we have created a table Players with a primary key on the ID column. The scripts to view the constraints and indexes information is shared below. We have created a new index on the primary key column. Read the inline comments for clarification.

-- CREATE TABLE 
CREATE TABLE PLAYERS(
    ID INTEGER,
    NAME VARCHAR2(50),
    RANK INTEGER,
    CONSTRAINT PK_PLAYERS PRIMARY KEY(ID)
);

-- VIEW CONSTRAINT INFORMATION
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME='PLAYERS';

-- VIEW INDEXES & VISIBILITY
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE TABLE_NAME='PLAYERS';

-- CHANGE VISIBILITY 
ALTER INDEX PK_PLAYERS INVISIBLE;

-- CREATE NEW INDEX
CREATE INDEX NEW_INDEX ON PLAYERS(ID);

Read: Oracle Database vs MySQL Database

Oracle add primary key sequence

It becomes inconvenient to fill the data every time for a primary key column. As you have to look for the previous value first then fill the next value in an oracle database table.

Using the oracle primary key sequence feature you can automate this process. Next time while inserting data you no need to worry about the primary key column as that will be automatically inserted.

Here is the syntax for creating a sequence in oracle database 19c & 21c. It is not mandatory to provide all the available options in sequence.

-- create sequence
CREATE SEQUENCE <SEQ_NAME>
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
CYCLE/NOCYCLE
CACHE 20;


-- delete sequence
DROP SEQUENCE <SEQ_NAME>

• Sequence can’t be created with CREATE SEQUENCE OR REPLACE <SEQ_NAME> , CREATE SEQUENCE OR ALTER <SEQ_NAME>, etc.
• It can be created and deleted only with the above mentioned syntax in oracle database 19c & 21c.
• .NEXTVAL method is used to retrieve the next number in the sequence.
• <SEQ_NAME>.NEXTVAL don’t get affected by the ROLLBACK command in oracle database 19c & 21c.If at the time of rolling back the sequence value was 7 so even after rollback it will continue from 7 which means next value will be 8.
• Use <SEQ_NAME>.NEXTVAL with the default to automatically generate values for that column.
• You can provide Null values during the sequence by manually inserting the null value.

Refer to our blog – Oracle Create Table with Sequence Primary Key

Oracle add primary key to existing table with data

Using the oracle Alter Table command primary key can be added to an existing table with data. But while doing so you will get the below error.

Error report -
ORA-01758: table must be empty to add mandatory (NOT NULL) column
01758. 00000 -  "table must be empty to add mandatory (NOT NULL) column"

The reason for this error is since the table already has data now when you are adding a column with a primary key constraint then these rows will be null but the primary key doesn’t allow null values.

Follow these steps to add primary key to existing table with data:

Step 1: Add a new column using Alter Table statement but without primary key constraint.

Step 2: Add data to a newly created column. Use UPDATE AGENT set AGENT_ID = ROWNUM; if you want to assign a row number.

Step 3: Add a primary key constraint to the column. Use Alter Table statement either with Add Constraint option or with Modify.

Example:

In the below example, we have created a table, inserted data, and then added a new column with data, and then converted the new column to primary key constraint.

-- CREATE TABLE
CREATE TABLE AGENT(
    AGENT_NAME VARCHAR2(100)
)

-- INSERTING DATA
INSERT INTO AGENT VALUES('JAMES BOND');
INSERT INTO AGENT VALUES('Julius');
INSERT INTO AGENT VALUES('Ethel Rosenberg');
INSERT INTO AGENT VALUES('Jonathan');

-- VIEW DATA
SELECT * FROM AGENT;

-- ADD PRIMARY KEY COLUMN
ALTER TABLE AGENT
ADD AGENT_ID INTEGER;

-- ADD DATA 
ALTER TABLE AGENT ADD AGENT_ID INTEGER;

-- UPDATE 
UPDATE AGENT set AGENT_ID = ROWNUM;

-- ADD PRIMARY KEY CONSTRAINT
ALTER TABLE AGENT
ADD CONSTRAINT PK_AGENT PRIMARY KEY(AGENT_ID);

-- VIEW DATA 
SELECT * FROM AGENT;

Read: Number Datatype in Oracle Database

Oracle add constraint primary key novalidate

Enable Novalidate method in oracle database 19c & 21c ignores the violation and responds normally to the query. This function understands that you are aware of the possible error and you will fix it in the future.

• Suppose you are giving presentation to the client, though you are confident about the code and already fixed all the bugs, errors and loose ends.
• Still to stay on the safer side novalidate option in the code.
• This way, no unexpected exception will occur and your program will run as per your expectations.

CREATE TABLE ADMIN(
    ADMIN_ID INTEGER,
    FIRST_NAME VARCHAR2(100),
    LAST_NAME VARCHAR(100),
    USERNAME VARCHAR(100) NOT NULL ENABLE NOVALIDATE,
    PASSWORD VARCHAR(100) NOT NULL ENABLE NOVALIDATE
);

Read: Connect to Oracle Database using Python

Oracle add composite primary key

In a table, multiple columns satisfy the eligibility criteria for a primary key. These columns are called composite key(s).

In other words, when in a table more than one column is assigned a primary key constraint then together these columns are called composite keys.

In this section, we will learn how to add composite primary keys in oracle databases 19c & 21c with an example.

In the below script, we have created a table America got talent and this table has the detail of all the participants. Columns P_ID, P_EMAIL, P_MOB are composite primary keys.

CREATE TABLE AMERICA_GOT_TALENT(
    P_ID INTEGER,
    P_NAME VARCHAR2(100),
    P_EMAIL VARCHAR2(100),
    P_MOB INTEGER,
    P_SCORE FLOAT,
    CONSTRAINT PK_COMPOSITE_KEY PRIMARY KEY (P_ID, P_EMAIL, P_MOB)
);

Read: How to create a database in Oracle 19c

Oracle add primary key constraint multiple columns

In a table, all the columns with the unique values are eligible to become a primary key but only one is called a primary key remaining are called a composite key.

Syntax:

Here is the syntax for adding primary key constraint on multiple columns in oracle database 19c & 21c:

CREATE TABLE <Table_name>(
    <col_1>  [data type],
    <col_2>  [data type],
    <col_3>  [data type],
    <col_4>  [data type],
    CONSTRAINT <constraint_name> PRIMARY KEY(col_1, col_2)
);

Example:

In our example, we have created a table Players which has 3 primary key columns (player_id, match_id, team_id). The bold line shows how to add a primary key to multiple columns in the oracle database.

CREATE TABLE PLAYERS(
    PLAYER_ID INTEGER,
    MATCH_ID INTEGER,
    TEAM_ID INTEGER,
    PLAYER_NAME VARCHAR2(50),
    COUNTRY VARCHAR2(50),
    GAME_NAME VARCHAR2(100),
    CONSTRAINT PK_PLAYERS PRIMARY KEY (PLAYER_ID, MATCH_ID, TEAM_ID)
);

Read: How to Fetch Data from Oracle Database in Python

Oracle how to add primary key to materialized view

A materialized view is a database object that stores query output and precomputed results. When there is a huge data then this process saves time involved in fetching the data.

There are mainly two reasons of using materialized view in oracle database 19c.

• To maintain the local copy for remote database object.
• To improve the performance of complex queries like join.

Below is the demonstration of oracle how to add a primary key to materialized view in oracle database 19c and 21c.

-- CREATE TABLE
CREATE TABLE EMP(
    deptno integer,
    empno integer, 
    ename varchar2(100),
    job varchar2(20)
);

-- CREATE TABLE
CREATE TABLE BACKUP AS
    SELECT deptno, empno, ename, job
    FROM EMP;


-- CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW MTV AS
    SELECT deptno, empno, ename, job
    FROM BACKUP 


-- ADD PRIMARY KEY TO MATERIALIZED VIEW
ALTER TABLE MTV 
ADD CONSTRAINT PK_MTV primary key (empno);

You may also like to read the following Oracle tutorials.

• Connect Excel to Oracle Database
• How to Check Oracle Database Version
• How to Get List all Tables in Oracle
• Oracle to_date Format [Complete Guide]

In this tutorial, we have learned how to add the primary key in oracle 19c and 21c. Also, we have covered these topics.

• Oracle add Primary key
• Oracle add primary key syntax
• Oracle how to add primary key
• Oracle how to change primary key
• Oracle how to add column to primary key
• Oracle add primary key using index
• Oracle add primary key sequence
• Oracle add primary key to existing table with data
• Oracle add constraint primary key novalidate
• Oracle add composite primary key
• Oracle add primary key constraint multiple columns
• How to add primary key to materialized view