How to add a primary key and a sequence in Oracle

First let’s create the table column that is going to serve as our primary key:

ALTER TABLE OUR_TABLE
ADD (
PKEY	NUMBER
)
;

Next, let’s create the sequence:

CREATE SEQUENCE CC_OUR_TABLE_PKEY_SEQ INCREMENT BY 1
MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE
;

then adding this sequence to the column to serve as a primary key:

UPDATE OUR_TABLE
SET PKEY = CC_OUR_TABLE_PKEY_SEQ.NEXTVAL
;

and finally adding the primary key constraint to the column:

ALTER TABLE OUR_TABLE
ADD CONSTRAINT OUR_TABLE_PK PRIMARY KEY (PKEY)
;

And here’s the rollback process:

--UNDO PROCESS
--ALTER TABLE OUR_TABLE DROP COLUMN PKEY;
--DROP SEQUENCE CC_OUR_TABLE_PKEY_SEQ;
--COMMIT;

Note: I like to use SQLDeveloper as an Oracle DB IDE that helps me visualise all the Oracle DB constructs.