• State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 13 replies
  • Answers 1 answer
  • Subscribers 9 subscribers
  • Views 9181 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

Query around Primary Key in Appian My SQL Database

komalc3
A Score Level 1

Hi All,

 

I am working on version 18.1.

 

I have a few queries around Primary Key in Appian's MY SQL database:

 

1. In my MySQL database, if I want to create two columns as primary key i.e. if I want to create a composite key rather than making just one column as a primary key then how can I do that as in my data type Appian allows to create only one column as primary key though in XSD we can add two columns as primary key using @Id annotation. So I would like to know how can I achieve this or I can only create one column as primary key.

2. Also would like to know in which scenario do I get Unique Key Constraint violated error.

 

Thanks in advance!!

  Discussion posts and replies are publicly visible

  • 0
    Certified Senior Developer
    hi
    if you are sure that the 2 column will make unique combination. then no need to declare primary key.
    in database table only one column can be primary key (if you define).

    instead, just declare column as other columns
    and at the time of query use 2 filters combination with AND.

    if you declare primary key that means its values must be unique otherwise you will get error.
  • 0
    A Score Level 1
     in reply to paragk998

    Hi  

    Thanx for replying!!
    My scenario requires that I need to make a composite key the primary key i.e. need to make two columns as primary key.

    Also when I open the data type, Appian allows to create only one column as primary key though in XSD (using @Id annotation) as well as in the MySQL table structure we can add two columns as primary key (as shown below).

    Any suggestions...

  • 0
    A Score Level 1
    Hi komalc,

    The constraints occur in the following cases:
    Databases can enforce constraints on column values that are not enforced by Appian on CDT field values.

    For example, a database may require a column value to be "not null", but the field in Appian can be empty. Be aware of these differences when designing your data stores.

    Use the attribute nillable="true" to prevent columns in your data store from enforcing a "non-null" constraint.


    You can create additional column to save combination values of (Value 1) and (value 2) Make that additional column as PK in your CDT.
    From DB side Additional Column should be Unique Key, PK is (value 1 and value 2)

    For more info , please have a look on docs.appian.com/.../Database_Schema_Best_Practices.html

    Thanks,
    ravalik
  • 0
    A Score Level 1
     in reply to ravalik
    Hi

    I am aware of the design practice!!
    But I am asking this question out of curiosity as I feel this is a limitation that we can have only one column as primary key in CDT be it an additional column which is a combination of two columns or a column in itself.

    Also any idea about in which scenario do I get "Unique Key Constraint violated error".
  • 0
    Certified Senior Developer
     in reply to komalc3

    Hi komalc

    in that case create CDT from xsd. or database table


    hope this will help.

  • 0
    A Score Level 1
     in reply to paragk998

    Hi paragk998

    Thanx for replying!!

    What I am trying to do is: I am modifying my existing CDT and uploading a new version of it wherein I want to make another existing column as primary key.

    The screen shot of table is (which is correct):

     

    Below is the screen shot with the error which I get when I try to upload new version of my CDT wherein I am making an existing column primary key with @Id annotation

     

    Am I missing anything, any suggestion...?

  • 0
    Certified Senior Developer
     in reply to komalc3
    check your xsd

    "MYAPPSecurity_DT1"
    this column may be having different data type in xsd and table

    I guess you took this as "Date" in DB and "string in XSD"

    it will be good if you can share your XSD.
  • 0
    Certified Lead Developer
     in reply to komalc3

    Composite keys are, in effect, not supported. The options for dealing with composite keys are documented here: docs.appian.com/.../Database_Schema_Best_Practices.html

  • 0
    A Score Level 1
     in reply to paragk998

    PFB the xsd:

     

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:MYAPP" targetNamespace="urn:com:appian:types:MYAPP">
  <xsd:complexType name="MYAPP_Security">
    <xsd:annotation>
      <xsd:appinfo source="appian.jpa">
        @Table(name="MYAPP_SECURITY")
	  </xsd:appinfo>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="id" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="firstName" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="lastName" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

     

    Screen shot of Table structure:

     

  • 0
    Hi ,

    While creating a CDT based on a database table If the database table contains multiple primary keys, your data store will not publish and you cannot connect to the table. You will have the opportunity to remove additional primary keys or select another column as the primary key. Appian recommends that you choose only one column as the primary key.

    As an alternative solution as suggested by Ravalik,
    You can create additional column to save combination values of Primary Key1 and Primary Key2. And make that additional composite column as PK in your CDT.
    From DB side Additional Column should be Unique Key, PK is ( Primary Key1 and Primary Key2)

© 2024 Appian. All rights reserved.