I was curious if anyone knows if it is possible to implement the
following in Oracle:
MySQL has a feature called Auto-Increment, which is similar to Oracle
sequences, however it was one some additional features that (I don't
believe) sequences can provide.
In MySQL, I can define the following table
create table REVISION
(
filename VARCHAR2(256) not null,
revision INTEGER not null AUTO_INCREMENT,
constraint REVISION_PK primary key (filename, revision)
);
After doing so, we can run the following inserts:
insert into revision(filename) values ('foo.txt');
insert into revision(filename) values ('bar.txt');
insert into revision(filename) values ('foo.txt');
insert into revision(filename) values ('foo.txt');
insert into revision(filename) values ('bar.txt');
we get the resulting table
filename revision
foo.txt 1
bar.txt 1
foo.txt 2
foo.txt 3
bar.txt 2
That is, a separate sequence is maintained for each filename. I have
not found a way to do this in Oracle, short of querying the table each
time for max(revision) where filename=? and incrementing it, but this
seems error prone. Is there native support for this, or would I be
forced to write some sort of trigger?
