Add a Primary Key to an existing table that has no unique data

DBA's and Developer's don't always agree. But one thing they should agree on is the existence of a primary key on every table. Agreeing and doing are two different things and I have on an occasion found myself having to create a primary key on a table that is already populate and has no unique attributes, or combination of, to place a primary key on.  Maybe you just want to convert a concatenated primary key to a primary key on a single column that does not exist.

These are the steps I use to make a new column and make it a primary key on an existing table that already has data.

What we are going to do is add a new, unique column that will auto-populate, for our primary key.

Table name:  MY_TABLE
One Index, one foreign key constraint
No trigger or primary key on the table

1. Create a new sequence
2. Create a new table with the existing data and new populated primary key
3. Rename the foreign key and index on the current table
4. Rename the original table to old and the new table to the original name
5. Recreate the index and recreate the foreign key
6. Create a new trigger to auto-populate our new column going forward
7. Grant permission on new table 
8. Add the new PRIMARY KEY

Create a new sequence used to populate the primary key

CREATE SEQUENCE MY_TABLE_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
NOCACHE
NOCYCLE
NOORDER;

Create a new table with a newly populated column to be the primary key
CREATE TABLE MY_TABLE_NEW
AS
(SELECT
 MY_TABLE _SEQ.NEXTVAL
 ID
, NAME
, DATA
, SCORE
FROM MY_TABLE);

Rename the existing Foreign Key
ALTER TABLE MY_TABLE
 RENAME CONSTRAINT
MY_TABLE _FK
 TO
 MY_TABLE _FK_OLD; 

Rename the existing Index

ALTER INDEX MY_TABLE _IDX
RENAME TO
MY_TABLE _FK_OLD;

Rename the original table to OLD, the new table to the original name 
ALTER TABLE MY_TABLE
RENAME TO
MY_TABLE _OLD;

ALTER TABLE MY_TABLE _NEW
 RENAME TO
 MY_TABLE;

Recreate both the index and the Foreign key
CREATE INDEX MY_TABLE _IDX
ON MY_TABLE _IDX
(NAME);


ALTER TABLE MY_TABLE
ADD (
CONSTRAINT MY_TABLE _FK
FOREIGN KEY (NAME)
REFERENCES NAMES (NAME)
ON DELETE CASCADE
ENABLE VALIDATE);
 
Grant permission to table

GRANT DELETE, INSERT, SELECT, UPDATE ON STRUCTURE_STATS TO PUBLIC; 

Create a new trigger to auto-populate our new primary key
CREATE OR REPLACE TRIGGER SGX.BIR_MY_TABLE
BEFORE INSERT on MY_TABLE
FOR EACH ROW
BEGIN
SELECT MY_TABLE _SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END; 

Add new PRIMARY KEY
ALTER TABLE MY_TABLE
ADD CONSTRAINT MY_TABLE_PK
PRIMARY KEY
(ID)
ENABLE VALIDATE;