Tuesday, June 25, 2013

adding primary key to already existing table in oracle

lets assume that there was a table ABC that was already existing in the database and you want to add an additional column with unique primary key values.


sql to create table abc :

  CREATE TABLE "ABC"
   (    "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "USER_ID" NUMBER NOT NULL ENABLE,
    "CREATED" DATE NOT NULL ENABLE )
   TABLESPACE "QUIKPAY_USER" ;




now we  can add an additional column ID which will be populated with all unique values.

alter table abc add(ID NUMBER);

you can create a sequence and get the values from the seq and insert them into table ID column:

CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
MINVALUE 1
NOCYCLE;


now insert the unique values into the database with below sql

UPDATE abc SET ID = SEQ_ID.NEXTVAL;


now you can make the column unique or add primary key to table,so that it wont take any more duplicate value into the table.

alter table abc add primarykey (ID);

No comments:

Post a Comment