Monday, July 3, 2017

Zumero and self-referencing SQL table

We've been using Zumero to synchronise between an on-premise database and devices which contain our UWP application.

Zumero works pretty well, but one issue I found was using integer identity primary keys in a foreign key relationship for columns in the same table.

i.e. Table.ColumnX references Table.PrimaryKeyColumn where the PrimaryKey column is an identity seeded integer.

Zumero is not able to support this relationship since at sync time, it doesn't retain referential integrity.
You're better off using a uniqueidentity (GUID) as the primary key in this case.