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.