Product Suggestion: DEPENDENT AUTOINCREMENT

You're probably familiar with DEFAULT AUTOINCREMENT which can be used to very simply, efficiently and safely initialize a numeric primary key column with the sequence 1, 2, 3, ...

If you use SQL Remote or MobiLink synchronization you're probably also familiar with DEFAULT GLOBAL AUTOINCREMENT which creates the partitioned sequence

• 1, 2, 3, ... for a database with SET OPTION PUBLIC.global_database_id = '0',
  

• 10000001, 10000002, 10000003, ... for a database with global_database_id = '1',
  

• 20000001, 20000002, 20000003, ... for a database with global_database_id = '2', and so on,
  

so that a primary key column can be globally unique across hundreds or thousands of separate databases.

But what about initializing columns in dependent tables, like line_number 1, 2, 3 within order_number 1, then line_number 1, 2, 3 again within order_number 2?

Suggestion: DEFAULT DEPENDENT AUTOINCREMENT

The DEFAULT DEPENDENT AUTOINCREMENT ( column-name ) clause would initialize the column to values 1, 2, 3 within each distinct value of another column-name in the same table, like this:

CREATE TABLE parent (
   pkey   INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data   INTEGER NOT NULL );

CREATE TABLE child (
   fkey   INTEGER NOT NULL REFERENCES parent ( pkey ),
   dkey   INTEGER NOT NULL DEFAULT DEPENDENT AUTOINCREMENT ( fkey ),
   data   INTEGER NOT NULL,
   PRIMARY KEY ( fkey, dkey ) );

BEGIN
   DECLARE @pkey INTEGER;
   INSERT parent VALUES ( DEFAULT, 1 );
   SET @pkey = @@IDENTITY;
   INSERT child VALUES ( @pkey, DEFAULT, 10 );
   INSERT child VALUES ( @pkey, DEFAULT, 20 );
   INSERT parent VALUES ( DEFAULT, 2 );
   SET @pkey = @@IDENTITY;
   INSERT child VALUES ( @pkey, DEFAULT, 30 );
   INSERT child VALUES ( @pkey, DEFAULT, 40 );
   COMMIT;
   SELECT * FROM parent ORDER BY pkey;
   SELECT * FROM child ORDER BY fkey, dkey;
END;

pkey        data        
----------- ----------- 
1           1           
2           2           


fkey        dkey        data        
----------- ----------- ----------- 
1           1           10          
1           2           20          
2           1           30          
2           2           40          

As with other kinds of AUTOINCREMENT columns, the @@IDENTITY connection-level variable would return the most recent value calculated across all columns; i.e, in the example above, @@IDENTITY would contain the successive values 1, 1, 2, 2, 1, 2 after each of the six INSERT statements.

---