Referential integrity / Coded Value Domains

Question

Hello!
Currently I have two problems to solve and I hope you can show me ways so solve them 😉

We are using an oracle database to store our map data and have established direct connections to edit the map in different versions.

Problem 1: We are using some additional (none versioned) tables to store key-value pairs. In ArcMap we would like to see the "values" instead of the keys. Google told me to use Coded Value Domains - but as I understood its not possible to integrate own database tables. Are there any ways to create those coded values domains "on the fly"?

Problem 2: We would like to use referential integrity with those Key-Value tables. Because of the versioning of the data it makes no sense to create foreign keys. Are there any solutions for this problem, too?

Regards

Benedikt

LukeWebb · Answer

This is probably highly unrecommended:

I use this SQL query to view my domain values outside an Arc environment. (MSSQL)

SELECT

codedValue.value('Code[1]','nvarchar(max)') AS "Code",

codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"

FROM sde.GDB_ITEMS AS items INNER JOIN sde.GDB_ITEMTYPES AS itemtypes

ON items.Type = itemtypes.UUID

CROSS APPLY items.Definition.nodes

('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)

WHERE itemtypes.Name = 'Coded Value Domain'

You can also limit it to just one of your domains like I have to view my domain "DOM_STATUS" code value pairs:

SELECT

codedValue.value('Code[1]','nvarchar(max)') AS "Code",

codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"

FROM sde.GDB_ITEMS AS items INNER JOIN sde.GDB_ITEMTYPES AS itemtypes

ON items.Type = itemtypes.UUID

CROSS APPLY items.Definition.nodes

('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)

WHERE itemtypes.Name = 'Coded Value Domain'

AND items.Name = 'DOM_Status'

Have never tried, but maybe you could try writing to these tables too -- I stick to using the Arc tools so far!

FreddieGibson · Answer

If you don't know ArcObjects you can also execute this task using out-of-the-box tools in Desktop/Pro and can easily automate this with python. You can see examples of the available tools below along with examples of they can be used.

Create Domain

http://desktop.arcgis.com/en/desktop/latest/tools/data-management-toolbox/create-domain.htm

Table to Domain

http://desktop.arcgis.com/en/desktop/latest/tools/data-management-toolbox/table-to-domain.htm

Add Coded Value To Domain

http://desktop.arcgis.com/en/desktop/latest/tools/data-management-toolbox/add-coded-value-to-domain.htm

Assign Domain To Field

http://desktop.arcgis.com/en/desktop/latest/tools/data-management-toolbox/assign-domain-to-field.htm

nicogis · Answer

To create the code value domain from table you can use arcobjects (http://help.arcgis.com/en/sdk/10.0/ArcObjects_NET/conceptualhelp/index.html#//0001000003mt000000) or Table To Domain  http://resources.arcgis.com/EN/HELP/MAIN/10.1/index.html#//001700000025000000 I understand what you mean but Geodatabase is an object relational ( http://desktop.arcgis.com/en/desktop/latest/manage-data/geodatabases/the-architecture-of-a-geodatabase.htm ) link useful: http://desktop.arcgis.com/en/desktop/latest/manage-data/gdbs-in-oracle/geodatabase-admin-vocabulary.htm