Application SQL tables

Application SQL tables must adhere to minimal definition rules, including primary keys with specific constraints, optional foreign keys for data consistency, and support for auto-incremented columns. CHECK and UNIQUE constraints can be defined in both the application database and the DB Sync schema, which manages configuration and data changes for remote databases.

SQL tables structure is untouched

The SQL tables used by the application database do not need to be modified for DB Sync system.

All configuration setups and data changes are registered in the DB Sync system tables.

Primary keys

Each SQL table must have a primary key with a maximum of three columns, and using the SMALLINT, INTEGER, BIGINT or CHAR/VARCHAR data types.

The (single or composite) primary key values must not exceed 50 characters.

Auto-incremented columns

SQL tables can have an auto-incremented column based on Informix-style SERIAL/BIGSERIAL types, or based on INTEGER/BIGINT types filled with values generated by a SEQUENCE object.

The DB Sync framework can handle auto-incremented columns automatically, as long as these are properly defined in the DB Sync database schema.

Foreign keys

SQL tables referencing other SQL tables should be defined with a foreign key. This is not mandatory, as you can define SQL table relations in the DB Sync database schema. However, it is best practice to use foreign key constraints in your application database, to let the SQL engine ensure data consistency.

In the DB Sync database schema, table relations need to be defined as a "master" or "property" relation. For example, a city table referencing rows of a country table must define a "property" foreign key relation. On the other hand, an order_line table (containing order lines), referencing an order table (containing order header information) must be defined as a "master" relation.

Check constraints

Application SQL tables can define CHECK constraints, that can also be defined in the DB Sync database schema.

The definition in the DB Sync database schema will be used when creating automatically the remote databases used by DB Sync client apps. Note however that CHECK constraints must use an SQL expression that in accepted by the central database server and the remote SQL engine (SQLite)

Unique constraints

Application SQL tables can define UNIQUE constraints, that can also be defined in the DB Sync database schema.

The definition in the DB Sync database schema will be used when creating automatically the remote databases used by DB Sync client apps.