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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s