I have a one to one relationship between WorkItem and Lead where workitemId is a foreign key in Lead. I first create workitem and then pass its id to lead while creating lead. Conditions in table, workitemId is unique and not null. This has been working fine in previous version of Write Record Smart service but in new version it is not working and giving error for workItemId not being there.
Lead: {leadId=505, workItemId=2843, qualityScore=4} This gives error: Unable to write to the source due to system error. Error Details: Field 'work_item_id' doesn't have a default value. Note: Same input still works with old version of write record smart service.
In logs I see it creates prepared statement without workitemId field:
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO `PRO_Lead` (`quality_score`, `lead_id`) VALUES (?, ?)]; Field 'work_item_id' doesn't have a default value; nested exception is java.sql.SQLException: Field 'work_item_id' doesn't have a default value
Discussion posts and replies are publicly visible
Yeah, I guess you don't have the primary key in the respective table in the database. Appian has recommended having default value or unique values in the database from the Appian Version 23.3. Add the primary or unique key with unique values.For More Info - https://docs.appian.com/suite/help/23.3/Appian_Release_Notes.html
I have found the root cause for this issue and got it fixed.I had not-null constraint in DB to maintain one-to-one relationship as each row in one table corresponds to one row in another table but seems that the internal processing logic of Appian is to maintain this relationship by itself and not depend on DB constraint. I removed this constraint and it started working.Even the same behaviour I observed for one to many relationship."Not Null" constraint affects the DB write in this case.