eXtremeSQL Autoid References

As discussed in the eXtremeDB User Guide, an autoid can be defined for a table to provide a unique number that is generated automatically when a new row is inserted. This type of internally incremented number is known in some popular DBMSs as an "auto-increment", "identity" or "sequence" value. Highly efficient object references can be implemented using an autoid column and referencing that column from a dependent object. eXtremeSQL supports the autoid (an 8 byte integer value) by using the autoid and reference keywords in declarations when creating the tables. For example:

 
    create table Company (autoid reference, userid integer primary key,
                    name string);
    create table Employee (userid integer primary key, name string,
                    company reference to Company);
     

Note that the reference field must have both autoid and reference in its declaration and must come before any other fields in the table. An automatically generated unique value will be inserted into the reference field when an insert statement is executed. The autoid counter is global for the database and not related to the table, so the actual autoid values for an individual table will not necessarily be sequential. And when inserting data into a table with an autoid, the autoid is not included in the column list.

For example:

 
    insert into Company values (10, 'ACME Enterprises');
    insert into Company values (20, 'Carrot Patch Inc.');
     

The reference values can then be displayed:

 
    select autoid, userid, name from Company;
    autoid  userid  name
    --------------------------------------------------------------------
    1       10      ACME Enterprises
    2       20      Carrot Patch Inc.
    Selected records: 2
     

In addition to the auto-generated reference values, an index (hash) is created to optimize lookups on the reference field. The reference values can then be inserted into the dependent table to create the foreign key reference:

 
    insert into Employee values (101, 'Wylie Coyote', 1);
    insert into Employee values (102, 'Road Runner', 1);
    insert into Employee values (201, 'Elmer Fudd', 2);
    insert into Employee values (202, 'Bugs Bunny', 2);
     

Then highly efficient queries joining the two tables can be executed that take advantage of the reference hash index. For example:

 
    select c.name as “Company”, e.userid as “Employee: Id”,
    e.name as “Name”
    from Company c, Employee e
    where c.userid = 10 and c.autoid = e.company;
    Company    Employee: Id Name
    --------------------------------------------------------------------
    ACME Enterprises        101     Wylie Coyote
    ACME Enterprises        102     Road Runner
     
    Selected records: 2
     

or

 
    select c.name as “Company”, e.userid as “Employee: Id”,
    e.name as “Name”
    inner join Employee e on c.autoid = e.company
    where c.userid` = 20;
    Company    Employee: Id Name
    --------------------------------------------------------------------
    Carrot Patch Inc.       201     Elmer Fudd
    Carrot Patch Inc.       202     Bugs Bunny
     
    Selected records: 2
     

Because the value of a reference field is auto-generated, it may not be known when inserting dependent records, as demonstrated in the examples above. For this reason the get_last_autoid()function is provided. It can be used as follows to insert “owner” and dependent objects:

 
    insert into Company values (10, 'ACME Enterprises');
    insert into Employee values (101, 'Wylie Coyote', get_last_autoid());
    insert into Employee values (102, 'Road Runner', get_last_autoid());
    insert into Company values (20, 'Carrot Patch Inc.');
    insert into Employee values (201, 'Elmer Fudd', get_last_autoid());
    insert into Employee values (202, 'Bugs Bunny', get_last_autoid());
     

Note that the value of field autoid generated when the Company object is inserted is retrieved by get_last_autoid()in the two subsequent inserts of Employee objects.