Im getting the error when writing to data store: ... " with identifier value `1007` does not exist " . This is the value of my primary key I'm trying to insert into the data store. I already have the primary key from user input and I'm trying to write all of the info into an empty data store. (I would share screenshot but it's work information). This is occurring within my process model when I am using the write to data store smart service. Why is this happening?
Discussion posts and replies are publicly visible
You’re getting the error because system treats a write with a primary key value as an update, not an insert. Since no record with that key exists, it fails.To fix this, when inserting new records, leave the primary key field blank so Appian and the database can auto-generate it. Only provide the primary key when updating an existing record. Additionally, ensure your database table and data store entity are configured to auto-generate primary keys. Verify in your process model that the primary key field isn’t populated during insert operations to avoid this error.
I dont think my PK within my sql database is set to auto increment, but even when setting the primary key to null prior to writing to the data store entity causes this problem. The ' ' identifier value is typically what the user would input
Your error happens because your database table's primary key is not set to auto-increment. When inserting, if you leave the primary key blank or null but the DB does Not auto-generate it, the insert fails - Appian shows an error with an empty identifier.
Have you checked the log file for more details? Error might be because of some other columns (failing the constraints) in that record.
Is there a way to set the primary key to auto increment within appian? I have a blank database within SQL server and I use that as a data source within appian so it seems that Appian copies the information over.
Appian cannot set up auto-increment for a primary key; this must be configured directly in your SQL Server database.
My two cents here
The identifier should not be left with users to input. It should be primary, and auto generating values on inserting a new row. If user is entering this value make this column/field NOT the identifier but just another column . Create another column which does not depend on user as the primary key with auto increment enabled.
Since you already have a cdt and thus table in database i suggest you to alter the table & cdt structure following data structure design best practices. The current design you have does not follow standard design principles.