Sure your solution provides the possibility to retrieve gapfree numbers. But if the application throws away the retrieved number, the data in the table itself will become gappy/gapped (?).
One solution is to add a before insert trigger, which retrieves the number.
My preferred solution is to bulletproof the datamodel itself via constraints. That way it will become impossible for any application to corrupt my business rule:
SQL> SET ECHO ON
SQL> alter session set nls_language=american;
Session altered.
SQL>
SQL> CREATE TABLE t
2 (text VARCHAR2(10),
3 rg# NUMBER,
4 rg#_previous NUMBER,
5 CONSTRAINT uk_rg#
6 UNIQUE(rg#),
7 CONSTRAINT uk_rg#_previous
8 UNIQUE(rg#_previous),
9 CONSTRAINT rg#_previous_fk
10 FOREIGN KEY (rg#_previous)
11 REFERENCES t(rg#),
12 CONSTRAINT rg#_gapfree
13 CHECK(
14 (
15 rg# IS NOT NULL
16 AND rg#_previous IS NOT NULL
17 AND rg# - rg#_previous = 1
18 )
19 OR
20 ( rg# IS NULL
21 AND rg#_previous IS NULL)
22 OR
23 ( rg# = 1
24 AND rg#_previous IS NULL)
25 ),
26 CONSTRAINT rg#_positiv
27 CHECK(sign(rg#) = 1)
28 )
29 /
Table created.
SQL>
SQL> INSERT INTO t VALUES ('dummy',NULL,NULL);
1 row created.
SQL> INSERT INTO t VALUES ('first',1,NULL);
1 row created.
SQL> INSERT INTO t VALUES ('second',2,1);
1 row created.
SQL> INSERT INTO t VALUES ('third',3,2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> INSERT INTO t VALUES ('fourth',-1,NULL);
INSERT INTO t VALUES ('fourth',-1,NULL)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_POSITIV) violated
SQL> INSERT INTO t VALUES ('fourth',7,NULL);
INSERT INTO t VALUES ('fourth',7,NULL)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_GAPFREE) violated
SQL> INSERT INTO t VALUES ('fourth',7,6);
INSERT INTO t VALUES ('fourth',7,6)
*
ERROR at line 1:
ORA-02291: integrity constraint (VSM_OWNER.RG#_PREVIOUS_FK) violated - parent key not found
SQL> INSERT INTO t VALUES ('fourth',7,3);
INSERT INTO t VALUES ('fourth',7,3)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_GAPFREE) violated
SQL> INSERT INTO t VALUES ('fourth',4,3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> DROP TABLE t;
Table dropped.
February 13, 2004 - 10:31 am UTC
gap free numbers are just a "bad" idea.
how many ways can you spell "serialization"
the requirement is generally found "to not be really a requirement" in *all* of my experiences. even where people tell me "its the law" (found not really to be true).
tell me -- what happened in the day of paper (to which people point to and say this is why we must, because we did in the past) when you
a) lost a tablet with some invoice numbers
b) spilled coffee on some, destroying them before using them
well -- you just accounted for them. that is all that ever needs be done.
but anyway -- if you want to build slow non-scalable systems, this certainly works. have fun with all that.
You need to show all of the code -- you have to
a) select max(rg#) out first
b) before you can insert rg# and rg#+1
c) only to get blocked cause someone else was doing a) at about the same time
d) to go back to a again once you get the "unique constriant violated" to try all over again.