Sunday, March 27, 2016

Create a PrimaryKey on existing table without unique values

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 "USERS" ;

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

alter table abc add(ID NUMBER);

We will now create a sequence and get the values from the seq and insert them into table ABC new 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 add unique constraint (or) add primary key constraint 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