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.