• State Not Answered
  • +1 person also asked this people also asked this
  • Replies 18 replies
  • Subscribers 8 subscribers
  • Views 12722 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

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

aswinb608
A Score Level 2
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.

OriginalPostID-202003

OriginalPostID-202003

  Discussion posts and replies are publicly visible

  • 0
    A Score Level 2
    Hi Philb,

    Please apologize me if I understood this wrong from Appian forum as mentioned in the link:forum.appian.com/.../Database_Schema_Best_Practices.html

    It asks me to denote that key with @Id which I did but seems like what I understood was wrong. Can you please explain the location where I need to put @Id in because I am not following the vague explanation on forum which doesn't have any examples.
  • 0
    Hi Ashwin,
    With reference to the XSD code provided by you, try with the code mentioned below :
    <xsd:element name="subCDT" nillable="true" type="tns:subCDTType">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">@Column(name="foriegn_composite_key_to_sub", columnDefinition="NUMBER(10,0)") @Id </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
  • 0
    Certified Lead Developer
    @aswinb608 You can't map a composite key in the way you are attempting to. I believe the @Id annotation can only be used with a single column of, for example, string or integer.

    You could select one of the composite keys and map that as an @Id, but you could see odd behaviour, such as only getting a single row when that key is actually repeated several times. This can actually work, provided any query against that table always uses that key.

    Your other alternatives (as mentioned above) are to have a view created that provides a unique identifier, or to use the query database smart service.
  • 0
    A Score Level 2
    Hi,
    I have several variants of this subCDT inside the main cdt based on the type....attached is a generalized version of the CDT....based on the column "type" inside "default1" cdt i have different types of environments called uat, prod, commercial, test etc which are of type "default1"....which is the subCDT....I had early attached @Id at the beginning before the @Column....seeing above from sonalk's code the only change I see is attaching @Id after @Column....but I still get that same error i mentioned above...attaching the xsd...

    default1.xsd

  • 0
    Certified Lead Developer
    I'll say it again - you can't map a subCDT as an ID as you are doing in that XSD.
  • 0
    Certified Lead Developer
    You need to map the @ID to a real column in the table or view and it can only map to one column. If your table does not have a unique column, one idea is to create a view that has a viewID column. That column can be a concat of the two columns with a separator in between (as 111 is not the same as 1_11 or 11_1). Then use this column as the @ID.
  • 0 in reply to ramakg

    when creating CDT (Data Type), choose "create from database table or view". If your database has the composite primary key, CDT also has it. 

  • 0
    Appian Employee
     in reply to AK47

    You can import it, but you can't actually use it. If you do this, you will see a message that says: "Data types without primary keys, or with more than one primary key, prevent data stores from publishing. In order to publish a data store that contains this data type, you must modify the data type so that it has only one primary key, then publish the data store. Otherwise, the data store will be saved as a draft."

    The CDT it self will be created but when you attempt to publish the data store it will fail. This means you can't read or write to the database, so the CDT itself becomes essentially useless. Composite keys are not supported in Appian when reading / writing to a database.

© 2024 Appian. All rights reserved.