Unable to verify 1 to M foreign key relationship for publishing data store

Hi sudhakar, did you use @column annotation.
Eg:
<xsd:element name="startDateAndTime" nillable="true" type="xsd:dateTime">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Column(name="startdate", columnDefinition="datetime")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

What does your CDT annotation look like? Here is an example mapping of a 1:M that works for me:

<xsd:element maxOccurs="unbounded" minOccurs="0" name="childTypes" type="tns:Child_Type">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@OneToMany(cascade=CascadeType.ALL, indexed=false) @JoinColumn(name="CHILD_FK_COL_NAME")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

For this example, the Child_Type table has a FK column named CHILD_FK_COL_NAME that points back to the parent table (the CDT)

What is the column that Appian expects? Is it something random or is it another column in your database?

Hi Justin, Thanks. I think this should work. I may have to modify the XSD and re import as a new version. I was little but confused on why the Verify button look into information_Schema.referential_constraints by table, referenced table attributes and validate the foreign key. I will try it out.

It expects the naming convention very similar to how some modeling tools would have automatically created based on the table and column names involved in the relation. I would not be certain on why such kind of verification is enforced.

Hi Sudhakar,

As Justin suggested we may define the foreign key in XSD definition, however since you are in v 17.2, I recommend to use data type designer which is user friendly and provide the exactly same foreign key defined in database in Parent CDT configuration. If not Appian it self creates the foreign key automatically.

As an example you may refer below CDT's screenshots configuration. Parent CDT: POC_LMS_employeeRecord and Child CDT: POC_LMS_employee_Leave_History with one to Many configuration. If you have foreign key already configured in database tick the check box (I know the name of the column(s) this field should use in the database) and provide the foreign key. If not Appian creates it's own.

Hope it helps you. Cheers..

Hi Sudhakar,

Update your XSD and properly define/map the foreign key. Publish your DS again and check DDL script. Thanks.