Hi, I am trying to map a composite key from a different CDT to another one

aswinb608

A Score Level 2

over 8 years ago

Hi,
I am trying to map a composite key from a different CDT to another one using one unique key inside xsd. How do I do it? Two columns from one cdt form a composite pair and I have another foriegn key coming from that table which defines the relationship. I want to map this foriegn key to that parent table inside CDT. What all JPA annotations should I use to get this behavior. I tried my best looking up online and troubleshooting with various things like @EmbeddedId, @Embeddable,@XmlInverseReference(mappedBy="fk_key")....but none of them parsed through appian cdt parser. Any help would be deeply appreciated.

Discussion posts and replies are publicly visible

0 ramakg
A Score Level 1
over 8 years ago

We are also having similar requirement. The database table contains the composite primary key (on 2keys). How to define a composite primary key in cdt?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jacob Cohen - Project Leader - Macedon Technologies over 8 years ago

Here is the documentation on composite keys: forum.appian.com/.../Database_Schema_Best_Practices.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
Certified Lead Developer
over 8 years ago

I think it's probably fair to say that Appian doesn't support composite keys.

What we've done in the past is have a view built over the table which adds a new column that provides a unique key per row - but that doesn't help much if you need to write to the table.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ramakg
A Score Level 1
over 8 years ago

The table is not belongs to us (which is created by some other department) and done have much control. But we have to insert the data through the Appian? Do we have any workaround solution?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ramakg
A Score Level 1
over 8 years ago

sorry typo. please read done->dont
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 aswinb608
A Score Level 2
over 8 years ago

I am getting an error when declaring additional variables in that cdt with @Id representing foriegn key to that composite key table...see attached...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
Certified Lead Developer
over 8 years ago

@ramakg Are you able to have the table owners add a stored procedure to insert to that table? You could then call that instead of doing a direct database write.

@aswinb608 Are you able to attach your XSD?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jacob Cohen - Project Leader - Macedon Technologies over 8 years ago

As stated in the documentation, "The Query Database Smart Service can read from and write to tables with composite keys." You may have to switch to that.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 aswinb608
A Score Level 2
over 8 years ago

Hi Philb,

Due to security constraints on the project I am not able to attach the xsd but I can provide a section of the code in a genralized format where the problem is occuring....here's the section:

<xsd:element name="subCDT" nillable="true" type="tns:subCDTType">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Id @OneToOne @Column(name="foriegn_composite_key_to_sub", columnDefinition="NUMBER(10,0)")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

The subCDT has two columns which pairs up to form a composite key and "foriegn_composite_key_to_sub" represents that pair....
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
Certified Lead Developer
over 8 years ago

@jacobc Good point, I forgot about the Query Database Smart Service :)

@aswinb608 You can't represent a composite key as you've done there. The error message is telling you that you can't use the @Id annotation in that fashion.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel