# eXtreme SQL 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.