Many-to-One relationship with composed key

[Former Member]

Hi all,

I have problems inserting an entity with a 1:n-relationship.

The relationship is defined over two columns. In detail:

There are two database-tables:

VERDECK

-

---

ID_PROJECT NUMBER (key)

ID_INTERN VARCHAR2(10 BYTE) (key)

UZSB

-

---

ID_PROJECT NUMBER (key)

ID_UZSB VARCHAR2(10 BYTE) (key)

ID_PROJECT_VERDECK NUMBER

ID_INTERN_VERDECK VARCHAR2(10 BYTE)

On UZSB there is a referential integrity defined between these tables:

FOREIGN KEY (ID_PROJECT_VERDECK, ID_INTERN_VERDECK)

REFERENCES R57SCHRAUB.VERDECK (ID_PROJECT,ID_INTERN))

This is mapped into JPA as follows:

@Entity

public class Verdeck implements Serializable {

@EmbeddedId

private VerdeckPK pk;

@OneToMany(cascade={CascadeType.ALL},mappedBy="verdeck")

private Collection<Uzsb> uzsbCollection;

...

}

-

---

@Embeddable

public class VerdeckPK implements Serializable {

@Column(name="ID_PROJECT")

private BigDecimal idProject;

@Column(name="ID_INTERN")

private String idIntern;

...

}

-

---

@Entity

public class Uzsb implements Serializable {

@EmbeddedId

private UzsbPK pk;

@ManyToOne

@JoinColumns({@JoinColumn(name="ID_PROJECT_VERDECK", referencedColumnName="ID_PROJECT"),@JoinColumn(name="ID_INTERN_VERDECK", referencedColumnName="ID_INTERN")})

private Verdeck verdeck;

...

}

-

---

@Embeddable

public class UzsbPK implements Serializable {

@Column(name="ID_UZSB")

private String idUzsb;

@Column(name="ID_PROJECT")

private BigDecimal idProject;

}

-

---

I try inserting a new entity VERDECK with some associated UZSBs as follows:

Verdeck einVerdeck = new Verdeck();

VerdeckPK myKey = new VerdeckPK();

myKey.setIdProject(idProject);

myKey.setIdIntern(lsId);

einVerdeck.setPk(myKey);

Uzsb lsUzsb = new Uzsb();

UzsbPK lsUzsbKey = new UzsbPK();

lsUzsbKey.setIdProject(idProject);

lsUzsbKey.setIdUzsb(lsId);

lsUzsb.setPk(lsUzsbKey);

lsUzsb.setVerdeck(einVerdeck);

Vector<Uzsb> uzsbColl = new Vector<Uzsb>();

uzsbColl.add(lsUzsb);

einVerdeck.setUzsbCollection(uzsbColl);

em.persist(einVerdeck);

-

---

As result I get the error

"ORA-02291: integrity constraint (R57SCHRAUB.SYS_C0022330) violated - parent key not found"

Does anybody have an idea? I think the problem could be the embedded key in Verdeck.

Perhaps I should take this into account in my JPA-definition of the relationship?

But how can I do this?

Regards,

Christoph

[former_member617985 · Accepted solution]

Hi Christoph,

our JPA implementation does not sort sqlstatements in the right order to work with foreign keys. This feature will come with the next release. The problem here is that databases check the constraint for every statement executed instead of checking at commit time (when our state is ok again).

As workaround you can set the settings for the foreign key constraints to deferred (pls. see oracle docu: alter table defferrable / alter session deffered) and the database will check at commit time. Second approach would be to delete the foreign key constraints.

hth

-Andreas