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))
- /