Assigning a primary key value to all rows from a sequence

Sometimes we need to add a primary key for the tables which does not have any. And primary key value comes from a sequence. As soon as we add primary key column and create sequence, here is a sample PL/SQL block we have to execute to assign a primary key value to all rows before enabling the primary key constraint. In this,

Table name : tab1

Primary key column that we added: col1

Sequence name from which primary key values are generated: seq1

 

DECLARE

  CURSOR c1 IS SELECT * FROM tab1 FOR UPDATE;

BEGIN

  FOR i IN c1

  LOOP

    UPDATE tab1 SET col1 = seq1.nextval WHERE CURRENT OF c1;

  END LOOP;

  -- COMMIT;

END;

/

From next time onwards, for every row insert if primary key value has to be auto incremented, here is the insert trigger.

CREATE OR REPLACE TRIGGER tabl1_ins_pk_trig

  BEFORE INSERT ON tab1 FOR EACH ROW

BEGIN

  SELECT seq1.NEXTVAL INTO :new.col1 FROM dual;

END;

/