AutoIncs

Different Solutions for a Common Problem

Many databases require auto increment numbers. While the most common need is probably to generate primary key values for referential integrity, there are other situations in which incrementing numbers sequentially can be useful. Regardless of why you might think of using auto increments, you should be aware that different RDBMs provide different solutions to this problem. Microsoft SQL Server lets you mark integer fields as identities and automates the increment transparently for you. Other systems like InterBase or Oracle don't have any specific field attribute or type that helps in this regard. Instead, they provide generators, which can be considered a persistent location to store a unique value that will be queried and incremented during an update. Generators are usually accessed from custom code or triggers, and their value is then manually assigned to a field.

Using Data Abstract's AutoInc Field Type

Data Abstract's AutoInc field type has been designed to hide and encapsulate the RDBM-specific details and provide a consistent development experience.

This is particularly useful when you need to support multiple databases (for example Microsoft SQL Server and Oracle), because it allows you to write one set of database-agnostic code that won't need to change or be modified to accommodate such differences.

The schema field contains two properties that are relevant for dealing with auto increments:

Data Type, which needs to be set to datAutoInc to enable Data Abstract's auto increment support.
Generator Name needs to be set to the name of the generator in your database, if your RDBMS is using generators. If your database supports AutoIncs natively, this property is not needed.

For example, in the Northwind database, Employee.EmployeeID can simply be marked as datAutoInc. In Firebird's Employees database, EMPLOYEE.EMP_NO would be marked as datAutoInc and Generator Name set to EMP_NO_GEN.

AutoIncs Handling when Inserting New Rows

When you insert new rows in an client-side data table, Data Abstract will generate incremental negative values to fill the AutoInc fields. Using negative numbers ensures that the client can generate new values that will not conflict with values generated on the server and without the need to keep track of the actual values generated on the server (which would be impossible in a disconnected client scenario).

Once the data is applied to the server, the Business Processors will handle the updating of the AutoInc fields to their proper values, depending on the database. If the database natively supports AutoIncs, it will simply insert the row and then read back the AutoInc value the RDBMS generated. If the database relies on generators instead, it will first query the specified generator for a new value, adjust the delta change and then insert the new row with the proper value.

In both cases, the AutoInc value generated by the database will then be written in the delta change that originated the insert and will be sent back to the client, allowing it to update the local data table without requiring a full refresh or another round trip to the server.

AutoInc Support and master detail tables

If you have tables like Northwind's Orders and Order Details, you are basically facing a situation in which both the master table and its details will contain negative AutoInc values that need to be synchronized. When you insert the first new Order, its OrderID value will be -1 (the next order would get -2, and so on). If you also insert Order Details, their foreign keys will be set to match the same negative numbers, to establish the proper relationships. So Order Details.OrderID will be -1, for any details added to the first order, -2 for the second, etc.

Data Abstract is capable of automatically handling these master/detail relationships when applying updates to the database by properly adjusting the detail relationship IDs appropriately before inserting.

If the remote Data Abstract service receives deltas for tables such as Order and Order Details, it will first insert the master records, as described above. After that, it will use the real IDs obtained during the insert, and replace the negative OrderID values in the detail deltas for you.

Master/Detail and Schema Relationships

For Data Abstract to provide this automatic handling of master/detail AutoIncs when applying updates on the server, a relationship needs to be defined between these two tables in the underlying schema.

Relationships can be added simply in Schema Modeler™ by dragging the Master table onto the Detail Table and the Modeler will calculate the fields which should be related (you can of course edit this). Alternatively, you can click on the Relationships button on the main toolbar of the Schema Modeler™ and manually manage relationships. On the Mac you use Server Explorer instead.

Without such a relationship, automatic synchronization of IDs will not happen automatically.