We are using MySQL DB, we have a use case where a text column in the DB table is having a UNIQUE constraint that accepts NULL, all values apart from NULLs have to be unique.While using write to DSE smart service, Appian product by default is converting the NULL value to empty value/empty string and mapping to table, due to which we are facing issues due to unique constraint while inserts.Please suggest a solution to overcome this issue.
Discussion posts and replies are publicly visible
I would be curious regarding your use case, as I always stay clear away from this type of setup, even though it is technically allowed per ANSI standards.
Are you able to modify your configuration to use a different column for the unique identifier, such as an auto-generated ID column?
We have the flexibility to modify the table configuration, but we have to make sure the text column specified has to have only unique values if any present
Great, this will allow you to handle validation in the user interface vs the DB level, which is always my preference. You can utilize an auto-generated ID column as the unique identifier (or any other unique/populated column), and handle validation on the form, something such as:
a!localVariables( local!dbValues: { a!map(textValue: "apple"), a!map(textValue: "orange") }, local!textValue, a!textField( label: "Enter Text", value: local!textValue, saveInto: local!textValue, validations: { if( or( rule!APN_isEmpty(local!textValue), /* Instead of contains(), use a!queryEntity() to verify against the DB */ not(contains(local!dbValues.textValue,local!textValue)) ), null, "Text value must be unique" ) } ) )
To verify against the DB, replace that contains() call with a rule that queries the DB that determines if the entered value is already present:
a!queryEntity( entity: cons!YOUR_DATA_STORE, fetchtotalcount: true, query: a!query( pagingInfo: a!pagingInfo(1,1), filter: a!queryFilter( field: "textValue", operator: "=", value: ri!textValue ) ) ).totalCount>0