Write to Data Store Entity (add) - ConstraintViolationException

I have found 2 ways how to solve this:

1. Delete table on your data server and recreate them using Appian data store - THIS WILL DELETE ALL YOUR DATA - I haven't tested this solution personally but this should work.

2. Recreate sequence used for that table and PK - this will not delete your data.

drop sequence "sequence_name_SQ";
CREATE SEQUENCE "sequence_name_SQ"
      INCREMENT BY 1
      START WITH 3110669; --This has to be the highest number from pk + 1

While you are adding new data (using insert SQL) and you provide pk by yourself the sequence is not increased. Instead of providing PK value NEXTVAL should be used as fallow:

INSERT INTO Orders_tab (Orderno, Custno)
    VALUES (Order_seq.NEXTVAL, 1032);

If you want to check what is the next sequence value use code:

select "sequence_name_SQ".nextval from dual;

Provided SQLs are compatible with Oracle DB only.

I have found 2 ways how to solve this:

1. Delete table on your data server and recreate them using Appian data store - THIS WILL DELETE ALL YOUR DATA - I haven't tested this solution personally but this should work.

2. Recreate sequence used for that table and PK - this will not delete your data.

drop sequence "sequence_name_SQ";
CREATE SEQUENCE "sequence_name_SQ"
      INCREMENT BY 1
      START WITH 3110669; --This has to be the highest number from pk + 1

While you are adding new data (using insert SQL) and you provide pk by yourself the sequence is not increased. Instead of providing PK value NEXTVAL should be used as fallow:

INSERT INTO Orders_tab (Orderno, Custno)
    VALUES (Order_seq.NEXTVAL, 1032);

If you want to check what is the next sequence value use code:

select "sequence_name_SQ".nextval from dual;

Provided SQLs are compatible with Oracle DB only.

One clarification - are you providing a PK number in your insert SQL statement? If the column is already set to auto-generate, then you don't need to provide any value for the PK in your insert statement at all; the database will automatically insert the row using the next available PK and increment the sequence.

Hi Peter,

when I'm using write to data store entity node then I don't provide any value for PK.

When I used Insert SQL i did it from Oracle SQLDeveloper not from Appian. In such Insert statement I have provided pk manually as value. This caused that pk value at the end was higher that nextValue from sequence.

Sorry if it was not clear from my first post. I'm glad that it is solved.

Ah gotcha, glad you got it to work!

Hi Furman,

In SQL Developer even has an option for Auto increment in identity column tab. You don't need to provide any value manually.

Although you try to insert data from SQL Developer or from process model, SQL will  automatically increment the identity value.

In this case, you don't need to delete any data from your table.

Hi,

I don't think it will work as you described. I have created tables using Appian v20.2 (tables were created in db based on save & publish button on Appian. In this way pk is created, sequence is created but pk is not set as auto increment column. As a consequence insert statement from sql developer requires either pk as value or sequence.NEXTVAL