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.