We have One to Many DB2 table with Auto Increment as Default. Both Primary key o

jegadeeswaran over 10 years ago

We have One to Many DB2 table with Auto Increment as Default. Both Primary key of Parent and Foreign Key of Child has been defied as Not Null in the DB2 table. We are getting SQL code 503 i.e. constraint violation when we insert data with Primary Key and Foreign Key as Null.
We have 2 below solution to fix this. Please let us know which is the right one as we need to take this approach to DBA.
-> Change the foreign key of Child table as Null from Not Null in DB2 table.
--> Change the Child table Auto Increment as Always from Table.

Discussion posts and replies are publicly visible

Parents

0 venkats533 over 10 years ago

take a look at the following XSD. XP_Product has one to many XP_Accounts. After publishing the imported CDTS, XP_ACCOUNT table will have a foreign key XP_ID which refers to parent id (primary key). The foreign constraints will be set automatically by the SQL generated by Appian data store verify/publish activity.

<xsd:schema targetNamespace="urn:com:xyz:types:project"
xmlns:cmpo1="urn:com:xyz:types:project" xmlns:xsd="www.w3.org/.../XMLSchema">
<xsd:complexType name="XP_Account">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                    @Table(name="XP_ACCOUNT")
           </xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="Id" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Id
                                        @GeneratedValue
                                        @Column(name="ID", nullable=false, columnDefinition="integer")
                     </xsd:appinfo>
<xsd:documentation>Primary key for account table</xsd:documentation>
</xsd:annotation>
</xsd:element>
<xsd:element name="AccountNumber" nillable="true" type="xsd:string">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Column(name="ACCOUNT_NUMBER", nullable=true, columnDefinition="varchar(20)")
                     </xsd:appinfo>
<xsd:documentation>Holds the Account Number</xsd:documentation>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="XP_Product">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                    @Table(name="XP_PRODUCT")
           </xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="Id" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Id
                                        @GeneratedValue
                                        @Column(name="ID", nullable=false, columnDefinition="integer")
                     </xsd:appinfo>
<xsd:documentation>Unique Product Id - Primary Key. </xsd:documentation>
</xsd:annotation>
</xsd:element>
           <xsd:element maxOccurs="unbounded" minOccurs="0"
name="AccountList" type="tns:XP_Account">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                         @OneToMany(indexed=false)
@JoinColumn(name="XP_ID")
                     </xsd:appinfo>
<xsd:documentation>Request's Account List</xsd:documentation>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 venkats533 over 10 years ago

take a look at the following XSD. XP_Product has one to many XP_Accounts. After publishing the imported CDTS, XP_ACCOUNT table will have a foreign key XP_ID which refers to parent id (primary key). The foreign constraints will be set automatically by the SQL generated by Appian data store verify/publish activity.

<xsd:schema targetNamespace="urn:com:xyz:types:project"
xmlns:cmpo1="urn:com:xyz:types:project" xmlns:xsd="www.w3.org/.../XMLSchema">
<xsd:complexType name="XP_Account">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                    @Table(name="XP_ACCOUNT")
           </xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="Id" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Id
                                        @GeneratedValue
                                        @Column(name="ID", nullable=false, columnDefinition="integer")
                     </xsd:appinfo>
<xsd:documentation>Primary key for account table</xsd:documentation>
</xsd:annotation>
</xsd:element>
<xsd:element name="AccountNumber" nillable="true" type="xsd:string">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Column(name="ACCOUNT_NUMBER", nullable=true, columnDefinition="varchar(20)")
                     </xsd:appinfo>
<xsd:documentation>Holds the Account Number</xsd:documentation>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="XP_Product">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                    @Table(name="XP_PRODUCT")
           </xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="Id" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                        @Id
                                        @GeneratedValue
                                        @Column(name="ID", nullable=false, columnDefinition="integer")
                     </xsd:appinfo>
<xsd:documentation>Unique Product Id - Primary Key. </xsd:documentation>
</xsd:annotation>
</xsd:element>
           <xsd:element maxOccurs="unbounded" minOccurs="0"
name="AccountList" type="tns:XP_Account">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
                                         @OneToMany(indexed=false)
@JoinColumn(name="XP_ID")
                     </xsd:appinfo>
<xsd:documentation>Request's Account List</xsd:documentation>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data