Auto incremented columns

SQL Tables using auto-generated integer primary keys need to be declared in the DB Sync database schema.

The DB Sync system use auto incremented column information to allow DB Sync client app to create new rows in the local database base, by using a negative number. When performing the sync procedure, the real new the DB Sync server will generate a new auto-incremented value.

The type of auto-incremented number generator is defined with the "autoincr" property in the table definition of your database schema.

The "autoincr" property must contain the following properties:
  • name: The name of the auto-increment (mainly used for SQL sequence names)
  • colname: The SQL column name the auto-increment applies to.
  • kind: The kind of auto-increment. Values can be "sequence", "serial", "bigserial" or "link".
  • start (optional): The first value to be generated.
  • origin (optional): When kind is "link", the origin property defines the source table and column containing of the auto-incremented value to be used for the new row.
In the next example, the auto-incremented generator is a sequence starting at 100000, for the customer_id primary column of the customer table:
"autoincr": {
    "name": "ai_customer",
    "colname": "customer_id",
    "kind": "sequence",
    "start": 100000
}
The kind of auto-increment generator can be:
  • sequence: An SQL SEQUENCE object produces the numbers. The sequence name is defined by the "name" property.
  • serial: The column is of type SERIAL (serial emulation may take place when not using an Informix server)
  • bigserial: The column is of type BIGSERIAL (serial emulation may take place when not using an Informix server)
  • link: Indicates that the generated number comes from the parent master table column specified in the "origin" property.
The next example shows an auto-incremented column specification for a "cclines" table, storing order lines. The primary key is composed by the order number "ccl_cusord" and a line number "ccl_line". By defining the kind of auto-increment as "link", the "ccl_cusord" values are copied from the referenced (master) table column defined in the"origin" property which is "cusord.cusord_id" in this example: 
"pkey": {
    "name": "pk_cclines",
    "cols": [ "ccl_cusord", "ccl_line" ]
},
"autoincr": {
    "name": "ai_cclines",
    "colname": "ccl_cusord",
    "kind": "link",
    "origin": "cusord.cusord_id"
},
"references": [
    {
        "name": "fk_cclines_cusord",
        "cols": ["ccl_cusord"],
        "ref_type": "master",
        "ref_table": "cusord",
        "ref_cols": ["cusord_id"],
        "on_delete": "deny",
        "on_update": "deny"
   }
]