I have an enterprise geodatabase in MS SQL Server that will be shared as a service for download to a custom application built on Esri's .NET sdk. We are using the sdk tools to take the data offline, make edits, and then sync back. The data is coming offline as a mobile (.geodatabase) file. We are using relationship classes for maintaining relationships during this process. However, we also want to be able to use a reporting server to make reports that will be accessing the tables in SQL Server directly. This reporting server has no understanding of relationship classes or the geodatabase structure. Because of this, we would like to maintain SQL relationships (PK and FK) in this database in addition to having our relationship classes. Is it possible to use both relationship classes and underlying DBMS relationships (PKs and FKs)?
I have already tried to add a relationship in the underlying DBMS and then prepare the feature class for sharing as an editable service, one step of which is to enable archiving. However I get the following error when I try to enable Archiving on the feature class:
Is it possible to get this to work? Does arcgis not allow for both relationship classes and DBMS relationships? It appears that I can add both the relationship class and DBMS relationship, but cannot enable archiving, which means I cannot publish an editable service from this feature class. Is there another solution?
Thanks for any assistance you can provide.
It is possible to get it to work, but you must be very careful that nothing will violate the constraints. According to referential integrity, you cannot have a foreign key value existing without a corresponding primary key value. Some processes in Esri applications will validate this, while SQL Server does not always validate this referential integrity.
When you enable archiving on nonversioned data, the geodatabase creates additional date attributes in the base table for that dataset. These date attributes are columns in the base table named gdb_from_date and gdb_to_date which are used to record the timestamp for the effective lifespan of the archived row. As edits are made to the dataset, these attributes are updated to maintain an historical record over time.
Those new fields have constraints on them. Just make sure you aren't violating any constraints.