Issue in Write to data store entity

Is there a reason the auto-increment field isn't set as the Primary Key? It's usually better practice to have an integer field as the PK, and you could certainly still make "ABC" a unique field even if it isn't the PK.

Hi Peter,

We don't want to display the Primary key field information to the user as it can be a security issue.

Hence on customer request we are creating one more column which we have kept as number and auto increment and same will be used for linking the record.

As per my knowledge, when we map the primary key in cdt then after selecting on the checkbox and marking it as auto increment we don't need to pass the same while using write to datastore entity.

so for requestNumber field we have given the below configuration in the XSD so that it can be auto genrated while writing the data

<xsd:element name="requestNumber" nillable="true" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@GeneratedValue @Column(name="RequestNumber", nullable=false, unique=true, columnDefinition="BIGINT IDENTITY")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

You should still make the PKID an auto-incrementing integer.  If you require a unique identifier that's exposed to the end user, just create that as an additional column and validate its uniqueness within Appian - that could be number, text, or both as per your requirements.

Hi Mike,

In our case idUnique is the primary key of type text which we are generating as unique value in Appian itself before writing to DB

For the requestNumber field we want it to be auto incremented so that it can be auto populated if we are passing this field as null from Appian.

We dont want to display the idUnique but want an option to filter the data using requestNumber, hence wants to create a relation in the same table where through the requestNumber I can identify the idUnique value as well.

Keep in mind that it's entirely up to you as the app developer to determine what fields are visible to users by choosing which fields to display on any UI - there's nothing stopping you from only showing the requestNumber and not showing the idUnique (regardless of which one is the primary key or not).

In general I'd strongly recommend having an integer as your PK because queries will perform much better using an integer. If you need the "idUnique" field to be unique, you can still add an annotation in your XSD for that too:

<xsd:element name="uniqueText" nillable="true" type="xsd:string">
  <xsd:annotation>
    <xsd:appinfo source="appian.jpa">@Column(name="unique_text", unique=true, columnDefinition="VARCHAR(255)")</xsd:appinfo>
  </xsd:annotation>
</xsd:element>

Keep in mind that it's entirely up to you as the app developer to determine what fields are visible to users by choosing which fields to display on any UI - there's nothing stopping you from only showing the requestNumber and not showing the idUnique (regardless of which one is the primary key or not).

In general I'd strongly recommend having an integer as your PK because queries will perform much better using an integer. If you need the "idUnique" field to be unique, you can still add an annotation in your XSD for that too:

<xsd:element name="uniqueText" nillable="true" type="xsd:string">
  <xsd:annotation>
    <xsd:appinfo source="appian.jpa">@Column(name="unique_text", unique=true, columnDefinition="VARCHAR(255)")</xsd:appinfo>
  </xsd:annotation>
</xsd:element>