DB table inserts failing due to UNIQUE constraint for a text field for NULL vs empty value

Question

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.

Chris · Answer

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

DB table inserts failing due to UNIQUE constraint for a text field for NULL vs empty value

Top Replies