how to disallow null attribute values in data entry for SDE layers and force unique values?

[KevinMacLeodCAI]

Is there a way to force users to enter an attribute value for a field or fields on a featureclass in SDE on Enterprise? Hopefully at the database level since it's being edited via featureservice in a custom-coded JS API viewers. (So clever workarounds in WebApp Builder won't be of help)  And also is there a way to force values to be unique? So if it's duplicated or null it will prompt the user, ideally.  I know there's a thing in AGOL to do both of these on hosted features layers so I am hoping there is also a way on ArcGIS Server 10.8.1 / SQL Server / SDE.

The 'allow Nulls' on a field in Catalog only converts nulls to spaces and we want them to pick from various domain values or in some cases enter text, etc. So that won't help. And even still, I tried switching that and it said it couldn't be done. I think from some reading, that has to be done at time of creation of a feature class.

I tried a few clever ideas such as making a Default Value that is not contained in a domain list. Catalog also kicked out an error on that and said the default value wasn't in the domain. Well of course! I guess maybe that used to be a way to do this but maybe doesn't work anymore.

I know SQL Server can have a unique property but I would want to leverage existing Esri methodologies first, if there are any.

[JohannesLindner]

This sounds like a job for constraint attribute rules.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/constraint-attribute-rules.... 

These won't let the user apply an insert or edit if it returns False, giving a custom error message instead.

// constraint rule for a filed that is non-nullable
// Triggers: Insert, Update
if(IsEmpty($feature.NonNullableField)) { return false }
return true

// constraint rule for a field that has to be unique
// Triggers: Insert, Update
var new_value = $feature.UniqueField
var new_gid = $feature.GlobalID
var fs = FeatureSetByName($datastore, "Database.DataOwner.FeatureClass", ["GlobalID", "UniqueField"], false)
fs = Filter(fs, "UniqueField = @new_value AND GlobalID <> @new_gid")
if(Count(fs) > 0) { return false }
return true

If you want the unique field to be a sequential field, you should use a calculation attribute rule instead:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/calculation-attribute-rules... 

// calculation attribute rule for a sequential field
// Triggers: Insert, Update
// create a database sequence first!
if(IsEmpty($feature.UniqueField)) {
  return NextSequenceValue("SequenceOwner.SequenceName")
}
return $feature.UniqueField

[KevinMacLeodCAI]

Thank you Johannes! That would be perfect. Except the service is not running on Portal. Is there any way to accomplish this for a regular feature service on ArcGIS Server? (10.7.1)

[JohannesLindner · Accepted solution]

Ah, ok.

The only other thing I'm aware of (which isn't to say that there aren't others) is the Attribute Assistant:

https://solutions.arcgis.com/shared/help/attribute-assistant/

If I remember correctly though, this works only in ArcMap in an active edit session, so it probably won't do for your App.

[KevinMacLeodCAI]

Thank you Johannes, that is what I figured, just wanted to check. Indeed I've used the Assistant and it's for Editing and we are doing a web app. I think we'll need to create database triggers.

[KevinMacLeodCAI]

I would propose a Feature Enhancement for both of these (requiring values; and enforcing unique values) for ArcGIS Server.  So customers without Portal could have both of these features. They are important capabilities for data collection with Collector and web apps, across a wide array of customers and industries. Utilities, fieldwork, etc.