Adding a surrogate key to an Oracle table

This works for me for a mid-sized table.

ALTER TABLE foo ADD ( foo_id integer )
/
CREATE SEQUENCE foo_id_seq
/
CREATE OR REPLACE TRIGGER before_update_foo
   BEFORE INSERT OR UPDATE
   ON FOO
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   l_foo_id   foo.foo_id%TYPE;
BEGIN
   IF :new.foo_id IS NULL
   THEN
      SELECT   foo_id_seq.nextval INTO l_foo_id FROM dual;

      :new.foo_id := l_foo_id;
   END IF;
END;
/
UPDATE   foo
   SET   foo_id = NULL
/
COMMIT
/
CREATE UNIQUE INDEX foo_ak
   ON foo( foo_id )
/
ALTER TABLE foo MODIFY ( foo_id NOT NULL )
/
ALTER TABLE foo ADD (CONSTRAINT foo_ak UNIQUE (foo_id))
/

Leave a Reply

Your email address will not be published.

Scroll to top