Ensuring the column follows the sequence in Oracle

Question

Is there a way to restrict a column (for example, "ID") to follow the created sequence (for example, "ID_SEQ")?

Without automatic restraint, hand inserts can kick out the entire sequence due to shocks. What can be done to fix this? Just double click NextVal?

Answer 1

It is not possible to create a declarative constraint for this kind of thing. Of course, you could create a trigger to the INSERT level that would automatically fill the key based on the sequence (ignoring any value that was provided if you were concerned about special inserts that do not use the sequence).

If you are in a situation where you need to increase the sequence, because someone ad hoc inserts created lines with keys that exceed the current value of the sequence, your two options:

• Create a loop that calls nextval several times
• ALTER , INCREMENT BY , , nextval , ALTER - . , . , - reset .

Answer 2

, . :

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;