Ensuring that column follows a sequence in Oracle

Question

Is there any way of constraining a column (say "ID") to follow a created sequence (say "ID_SEQ")?

Without an automatic constraint, manual inserts might throw the entire sequence out-of-whack. What can be done to fix this? Just call NextVal twice?

Answer 1

You can use a trigger to accomplish this. Example:

create or replace trigger product_insert before insert on product for each row begin
select id_seq.nextval
into :new.product_id
from dual;
end;

Answer 2

There is no way to create a declarative constraint for this sort of thing. You could, of course, create a BEFORE INSERT row-level trigger that would automatically populate the key based on the sequence (ignoring any value that was provided if you're concerned about ad hoc inserts that don't use the sequence).

If you are in a situation where you have to increment a sequence because someone's ad hoc inserts created rows with keys greater than the current value of the sequence, your two options are

1. Create a loop that calls nextval repeatedly
2. ALTER the sequence to set the INCREMENT BY to whatever large value you need, call nextval once, and ALTER the sequence back. This requires that no one else is using the sequence at the same time, though. But it is useful if you need to do something like reset a bunch of sequences after refreshing data from a different environment.