Adding a Primary/Unique Key to an Existing Table in Oracle

TL;DR: Learn how to add a unique or primary key to an existing table and update its values using a sequence. This guide includes practical steps with PL/SQL code examples to automate the process of assigning sequential IDs to an existing table.

Introduction

In Oracle, adding a primary or unique key to an existing table is a common task when you want to enforce uniqueness for a column, such as an ID column. Sometimes, the table might already contain data, and you need to update the ID column to assign sequential values. This guide will walk you through how to achieve this by creating a sequence, updating the table with sequential IDs, and automating the process with triggers.

Step 1: Create a Table

Let’s start by creating a simple table without any primary key or unique identifier:

CREATE TABLE abc (
    id NUMBER,
    name VARCHAR2(20)
);

In this case, we only have the name column populated, and the id column is left empty.

Step 2: Insert Data into the Table

Next, we insert some sample data into the name column:

INSERT INTO abc (name) VALUES ('a');
INSERT INTO abc (name) VALUES ('b');
INSERT INTO abc (name) VALUES ('c');
INSERT INTO abc (name) VALUES ('d');
INSERT INTO abc (name) VALUES ('e');
INSERT INTO abc (name) VALUES ('f');
INSERT INTO abc (name) VALUES ('g');
INSERT INTO abc (name) VALUES ('h');

At this stage, the id column remains empty while the name column holds values.

Step 3: Create a Sequence for Unique IDs

Now, we will create a sequence that will be used to generate unique IDs for each row in the id column:

CREATE SEQUENCE SEQ_abc
    START WITH 1
    MAXVALUE 99999
    MINVALUE 1
    NOCYCLE
    NOCACHE
    NOORDER;

This sequence will start at 1 and will increment with each use to assign a unique value to the id column.

Step 4: Update the ID Column with Sequential Values

Next, we will write a PL/SQL block that will update the id column for each row using the sequence SEQ_abc:

DECLARE
    CURSOR store_id IS
        SELECT id FROM abc FOR UPDATE;
BEGIN
    FOR c_store_id IN store_id LOOP
        UPDATE abc
        SET id = SEQ_abc.nextval
        WHERE CURRENT OF store_id;
    END LOOP;
    COMMIT;
END;

Note: This update operation is a one-time process. Once the IDs are updated, you can set up a trigger to automatically generate sequential IDs when new records are inserted into the table.

Step 5: Automating with a Trigger

To automate the process of updating the id column every time a new row is inserted, you can create a trigger:

CREATE OR REPLACE TRIGGER trg_abc
    BEFORE INSERT ON abc
    FOR EACH ROW
BEGIN
    :NEW.id := SEQ_abc.nextval;
END;

This trigger will automatically assign a sequential ID to any new row inserted into the abc table.

Conclusion

Adding a primary or unique key to an existing table in Oracle can be done by generating sequential IDs using a sequence and updating the table with PL/SQL. If you need the IDs to be automatically assigned for future inserts, a trigger can automate the process. This method ensures that all records in your table have a unique identifier and can be used for further reference or as primary keys in your database.