How_to_Add_a_Foreign_Key_Constraint

How to Add a Foreign Key Constraint

Question

In our code, we have a USER table and a CONNECTIONS table we need a foreign key in the CONNECTIONS table which should refer to the ID column in the USER table.

<Connection>
    <User id="Scott Tiger"/>
</Connection>
...
<User id="Scott Tiger">
    <login>scott</login>
    <password>tiger</password>
</User>

Issue: HJIII-86.

Answer

Associations are normally modelled in JPA with @OneToMany, @ManyToOne and so on. In the question above the requirement is actually not to model the association, but to simply add a foreign key constraint. To have something like:

alter table CONNECTION_TABLE
  add constaint FK1DB6BCCB5C8B695A
  foreign key (USER_ID) references USER_TABLE;

This has quite little to do with HJ3 or JPA since this foreign key does not exist in JPA entities, it's purely a database thing. If you still want this foreign key to be generated with JPA means, you can add a secondary table annotation to the entity which maps onto the referenced table.

Here's an example from ejb/tests/issues:

    <xsd:complexType name="issueHJIII86AType">
        <xsd:annotation>
            <xsd:appinfo>
                <hj:entity>
                    <orm:secondary-table name="ISSUEHJIII86BTYPE">
                        <orm:primary-key-join-column name="A_" referenced-column-name="ID"/>
                    </orm:secondary-table>
                </hj:entity>
            </xsd:appinfo>
        </xsd:annotation>
        <xsd:sequence>
            <xsd:element name="login" type="xsd:string" minOccurs="0" />
            <xsd:element name="password" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
        <xsd:attribute name="id" use="required" type="xsd:string">
            <xsd:annotation>
                <xsd:appinfo>
                    <hj:id>
                        <hj:generator generatorClass="assigned" />
                    </hj:id>
                </xsd:appinfo>
            </xsd:annotation>
        </xsd:attribute>
    </xsd:complexType>

    <xsd:complexType name="issueHJIII86BType">
        <xsd:sequence>
            <xsd:element name="A" type="xsd:string"/>
        </xsd:sequence>
        <xsd:attribute name="id" use="required" type="xsd:string">
            <xsd:annotation>
                <xsd:appinfo>
                    <hj:id>
                        <hj:generator generatorClass="assigned" />
                    </hj:id>
                </xsd:appinfo>
            </xsd:annotation>
        </xsd:attribute>
    </xsd:complexType>

Here's a sample XML:

    <issueHJIII86A id="issueHJIII86A[0]">
        <login>scott</login>
        <password>tiger</password>
    </issueHJIII86A>
    <issueHJIII86B id="issueHJIII86B[0]">
        <A>issueHJIII86A[0]</A>
    </issueHJIII86B>

In this example ISSUEHJIII86BTYPE has a foreign key referencing ISSUEHJIII86ATYPE. To achieve this, I've added a secondary table to the IssueHJIII86AType, referenced entity. The name of the secondary table matches the name of the table of the referencing entity. Primary key join column estabilishes a reference between the id column of the referenced entity (IssueHJIII86AType.Id) and the FK column of the referencing entity (IssueHJIII86BType.A). This results in a desired foreign key constraint:

alter table ISSUEHJIII86BTYPE 
        add constraint FK42DC84B0182F5598 
        foreign key (A_) 
        references ISSUEHJIII86ATYPE;

Icon In order not to violate this constraint you'll have to make sure that your entities are persisted in the right order.