Link to home
Start Free TrialLog in
Avatar of JoshWegener
JoshWegener

asked on

ORACLE Replacing a PK in a table

I have a table called T1... T1 has a combined PK of C1 and C2... I can remove the PK via "ALTER TABLE T1 DROP CONSTRAINT pk_T1;". I created the new sequence called S1. I added the new PK "ALTER TABLE T1 ADD CID NUMBER(11);

Now, how can I update the PK using the new sequence?
ASKER CERTIFIED SOLUTION
Avatar of dbmullen
dbmullen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JoshWegener
JoshWegener

ASKER

I don't understand how to use that.
Ok, I did some google searching... this seems to work...

ALTER TABLE T1 DROP CONSTRAINT pk_C1;
CREATE SEQUENCE T1_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

ALTER TABLE T1 ADD PKID NUMBER(11);

CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
  select T1_seq.NEXTVAL into :new.PKID from dual;
END;
/

UPDATE T1 SET PKID = 0;
DROP TRIGGER before_insert_trigger;

ALTER TABLE T1 ADD CONSTRAINT pk_PKID PRIMARY KEY ( PKID );
COMMIT;