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>

Related Issue: HJIII-86

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 establishes 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;

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