If the target database type is SQL Server, the Write Records smart service will fail when writing a new record with a null primary key. The following error will be seen in the process history and tomcat-stdOut.log:
Unable to write to or delete from the source due to a data integrity violation. Error Details: Cannot insert the value NULL into column <column>, table <table>; column does not allow nulls. INSERT fails.
In 23.4, the Write Records smart service was updated to allow non-nullable foreign keys. This changed how the Write Records smart service decides what needs to be an INSERT vs. an UPDATE query in the database.
INSERT
UPDATE
As a side effect, due to how the SQL Server IDENTITY columns work, the Write Records node fails if the primary key is explicitly defined to be NULL in either the SAIL code or the process model.
IDENTITY
NULL
Please note this only applies if it is a SQL Server database.
Update the design to prevent NULL from being explicitly defined as the primary key value in the SAIL code or process model.
This article applies to Appian 23.4 and later.
Last Reviewed: March 2024