Maintaing Referential Integrity in ArcSDE

[MarkWilloughby]

Hello:

The organization I work for is in the midst of migrating all of our infrastructure asset data into ArcSDE from a foreign system. We are in the data model planning and review stage and have based our data model on the ArcGIS for Local Government data model templates.

There is a business need to have numerous related tables attached to our spatial features to track the lineage of some of the attributes that will get entered. We also need to generate sequences for primary and foreign keys, check for and enforce uniqueness of those generated sequences and finally, there will be a need to invoke some stored data management procedures on insert or update of records feature classes and/or the related tables.

We are using ArcSDE with MS SQL Server 2012. In a multi-user versioned environment, what are todays best practices for managing this:

-using database level tools (triggers, constraints, sequences, etc.) on the SDE.DEFAULT tables (probably a bad idea);
-using database level tools on multi-version views;
-using ArcGIS desktop or other custom developed application level tools instead of database level tools;
-some combination of part or all of the above.

I would prefer to have referential integrity enforced at the database level but my intuition tells me that the multi-user versioned environment will get in the way of that.

Your feedback is much appreciated.

Thanks,

Mark

[NickHetrick]

I am currently working on a very similar issue. I have a replicated environment and also have integration with an Asset Mgmt solution that relies on integrity of an AssetID column. I am actually working up some scenarios to see what might work. One thing that I am looking into is using the SEQUENCE functionality in SQL Server 2012 similar to your first option you listed. I am not sure if this would work with replication but I am going to do some testing to see if it is a possibility.

[Anonymous User]

Hello Mark

In my opinion, If you need to maintain a versioned environment database tools/rules won't help you. Once layers/tables are registered as versioned, changes (insert/update/delete) are not stored directly in base tables so you will not be able to capture events over the these tables nor ensure referencial integrity.

My recommendation is use the geodatabase model to enforce integrity/relationships as far as you can ( ArcGIS Desktop ) and ArcGIS Desktop as editing tool.

Hope this helps

Jesús de Diego

[AbhishekSingh]

Hi Mark,

- It is fine to use the database level tools on sde.default, or in the other words on the business/base table. This can be done by registering the data as versioned (with move edits to base option). Advantage of doing this is that all the edits that you make will go into the delta (adds and deletes) tables as well as the base table, this way you will be able to access all the recent edits without being dependent on the compress process. The disadvantage is that you will not be able to use this data with some functionalities like Replication, Archiving etc..

Refer the following web-link for more information on this - 

A quick tour of registering and unregistering data as versioned

ArcGIS Help 10.1

- Multi-versioned views are accessible from the database level, therefore you can access them for sure, however there will be some dependency on what database level tools you want to run on them. Mostly, there will be no issues with it since the data created in ArcCatalog is anyway a part of the database.

- Once you register the data as versioned (with move edits to base option) you will have all the data present in the base table. This will allow you use database tools or third party tool on the feature class, however it is not recommended to edit this data using third party tools, try using only ArcMap for this.

- If I understand correctly, since you are using third party tools using OBJECTID to maintain referential integrity might not be accurate therefore plan for another field that can be effectively used to relate with all the tables. Having another field (with unique values) I think will be the best way for doing this.

- Abhishek

[Anonymous User]

mmm....the "move edits to base" options only applies to DEFAULT version (editions made in this version or merged from other versions to DEFAULT version).

So, long transactions in other versions different than DEFAULT could violate database referential integrity rules.  Am I wrong?

Jesús

[nicogis]

Integrity implemented at the geodatabase level (for example: relationship class, subtype, domain etc) are recognized by ArcObjects applications. SDE is an application server that add new capabilities RDBMS (versioning, archiving ect). SQL integrity are enforced by the level database.

Versioned edits function differently from standard database edits, so the integrity constraints may not function as expected.

I think that if you don't need versioning, archiving ect. you can use capabilities spatial native your rdbms so you can use IR level db. If you need versioning and you want manage your system with integrity of geodatabase  (for example: relationship class, subtype, domain etc) you need an application  that recognize it (arcgis desktop /arcobjects). Hybrid solution you can risk of having problems and need caution.

[847396730]

This is fairly well after the initial post, but in case it helps:

1) You can use triggers and stored procedures on the Add and Delete tables--you do not need to register with option to move edits to base to take advantage of those two db tools.

2) Unique IDs in the SDE environment are GlobalIDs and other GUID fields you create.  A relationship class facilitates one-to-one, one-to-many, or many-to-many relationships, using GUIDS (or other fields, but GUIDS are recommended). GUIDS and relationship classes are SDE's answer to the Primary Key/Foreign Key concept.

3) There are some things you can't do exactly as you would in a conventional database.  Lookup tables are a good example.  Domains and subtypes provide similar functionality, but in some cases are simply not practical.

4) You do not have to store all of your enterprise's data in a geodatabase.  It is often logical to keep some things in a conventional database and use other methods to integrate the SDE data with the tabular business data.