INSERTing into tables with serial/autoincrement columns

`INSERTing` into tables with serial/autoincrement columns
Prev	Chapter 12. `INSERTing` into a resultset	Next

`INSERTing` into tables with serial/autoincrement columns

“primarykey="1"” in the schema file specifies a primary key column. “primarykey="serial"” in the schema file specifies a primary key that's an auto-incremented serial number column. This would be an “auto_increment” MySQL column and a “serial” or a “bigserial” PostgreSQL column:

<schema>

  <table class="accounts">
    <column name="account_id" datatype="int64_t" primarykey='serial' />
    <column name="code" />

    <join class="ledger_entries">
      <column>account_id</column>
    </join>
  </table>

  <table class="transactions">
    <column name="transaction_id" datatype="int64_t" primarykey='serial' />

    <join class="ledger_entries">
      <column>transaction_id</column>
    </join>
  </table>

  <table class="ledger_entries">
    <column name="ledger_entry_id" datatype="int64_t" primarykey='serial' />
    <column name="account_id" datatype="int64_t"/>
    <column name="transaction_id" datatype="int64_t"/>
    <column name="ledger_date" datatype="x::ymd"/>
    <column name="amount" datatype="double"/>

    <join class="accounts" type="inner join" only="1">
      <column>account_id</column>
    </join>

    <join class="transactions" type="inner join" only="1">
      <column>transaction_id</column>
    </join>
  </table>
</schema>

Normally, insert() requires the values for all primary key columns, but the value of a “serial” primary key columns is not required. This is usually the case, and the new row returned by insert() gives the value of the serial column in the new row:

auto accounts_rs=accounts::create(conn);
auto transactions_rs=transactions::create(conn);

auto first_account=accounts_rs->insert("code", "Code1");
auto second_account=accounts_rs->insert("code", "Code2");

auto transaction=transactions_rs->insert();

auto ledger_entries_rs=transaction->join_ledger_entries();

auto first_entry=ledger_entries_rs
    ->insert("account_id", first_account->account_id.value(),
             "ledger_date", x::ymd(),
             "amount", 10);
auto second_entry=ledger_entries_rs
    ->insert("account_id", second_account->account_id.value(),
             "ledger_date", x::ymd(),
             "amount", -10);

This example creats two new rows in the accounts table, and one new row in the transactions table. Note that transactions table's insert() takes no parameters. The only column in the transactions table is a serial primary key, so nothing else is needed.

Afterwards, two new rows get created in the ledger_entries table. Both rows have their transaction_id column's value set to the primary key in the new transactions row (since they get inserted via a nested resultset from the transactions table. Each row also sets a corresponding value for its account_id column, from one of the two new rows in the accounts table.

Note

At this time, serial primary keys are implemented only with MySQL and PostgreSQL databases.