Part of foreign key in primary key

We have a number of tables related to each other with foreign key constraints. We are modelling these constraints as relationships in entity beans. As an example for the problem encountered, I am providing 3 of the tables involved in the relationships. For simplicity, I have removed some of the irrelevant fields from the tables.

CREATE TABLE TREG_PARTY (
CPP_ID VARCHAR2 (100) NOT NULL,
PARTY_ID VARCHAR2 (50) NOT NULL,
ENABLED VARCHAR2 (10),
CONSTRAINT PK_TREG_PARTY
PRIMARY KEY ( CPP_ID, PARTY_ID ) ) ;

----------------------------

CREATE TABLE TREG_SECURITY (
CPP_ID VARCHAR2 (100) NOT NULL,
SECURITY_ID VARCHAR2 (50) NOT NULL,
PARTY_ID VARCHAR2 (50) NOT NULL,
ROLE VARCHAR2 (10),
HASH_FUNCTION VARCHAR2 (100),
CONSTRAINT PK_TREG_SECURITY
PRIMARY KEY ( CPP_ID, PARTY_ID, SECURITY_ID ) ) ;

ALTER TABLE TREG_SECURITY ADD CONSTRAINT FK_TREG_SECURITY3
FOREIGN KEY (CPP_ID, PARTY_ID)
REFERENCES TREG_PARTY (CPP_ID, PARTY_ID) ;

---------------------------

CREATE TABLE TREG_CPP_DOC_EXCHANGE (
CPP_ID VARCHAR2 (100) NOT NULL,
DOC_EXCHANGE_ID VARCHAR2 (50) NOT NULL,
PARTY_ID VARCHAR2 (50) NOT NULL,
RECEIVER_SECURITY_ID VARCHAR2 (50),
SENDER_SECURITY_ID VARCHAR2 (50),
CONSTRAINT PK_TREG_CPP_DOC_EXCHANGE
PRIMARY KEY ( CPP_ID, PARTY_ID, DOC_EXCHANGE_ID ) ) ;

ALTER TABLE TREG_CPP_DOC_EXCHANGE ADD CONSTRAINT FK_TREG_CPP_DOC_EXCHANGE1
FOREIGN KEY (CPP_ID, PARTY_ID, SENDER_SECURITY_ID)
REFERENCES TREG_SECURITY (CPP_ID, PARTY_ID, SECURITY_ID) ;

ALTER TABLE TREG_CPP_DOC_EXCHANGE ADD CONSTRAINT FK_TREG_CPP_DOC_EXCHANGE2
FOREIGN KEY (CPP_ID, PARTY_ID, RECEIVER_SECURITY_ID)
REFERENCES TREG_SECURITY (CPP_ID, PARTY_ID, SECURITY_ID) ;

ALTER TABLE TREG_CPP_DOC_EXCHANGE ADD CONSTRAINT FK_TREG_CPP_DOC_EXCHANGE3
FOREIGN KEY (CPP_ID, PARTY_ID)
REFERENCES TREG_PARTY (CPP_ID, PARTY_ID) ;

------------------------

As can be seen, TREG_SECURITY and TREG_CPP_DOC_EXCHANGE tables have a foreign key constraint with TREG_PARTY table. And TREG_CPP_DOC_EXCHANGE has two foreign key constraints with TREG_SECURITY table.

The compound primary key of TREG_CPP_DOC_EXCHANGE ( CPP_ID, PARTY_ID, DOC_EXCHANGE_ID ) has two of the fields ( CPP_ID, PARTY_ID ) in common with the two foreign keys specified by (CPP_ID, PARTY_ID, *_SECURITY_ID). When this is modelled in entity beans as relationships, the foreign key relationships are represented as CMR-fields and the columns themselves are CMP-fields.

During the creation of CppDocExchangeBean (Entity bean of TREG_CPP_DOC_EXCHANGE), we face a problem. If the entire foreign key is part of the primary key, then we can simply use the setXXX methods in the ejbCreate() for all the columns and the CMR-fields are not set. If the entire foreign key is NOT part of the primary key, then we can use setXXX methods of the CMR-fields in ejbPostCreate().

Since a part of the foreign key forms a part of the primary key, we are unable to set the SENDER_SECURITY_ID and RECEIVER_SECURITY_ID in either ejbCreate() or ejbPostCreate().

When we try to set in ejbCreate(), we hit the following error :

Caused by: javax.ejb.TransactionRolledbackLocalException: EJB Exception:; nested exception is: javax.ejb.EJBException: [EJB:010145]When a cmp-field and a cmr-field (relationship) are mapped to the same column, the setXXX method for the cmp-field may not be called. The cmp-field is read-only.

When we try to set the CMR fields in the ejbPostCreate(), we hit the following error :

Caused by: javax.ejb.TransactionRolledbackLocalException: EJB Exception:; nested exception is: javax.ejb.EJBException: [EJB:010146]The setXXX method for a cmr-field that is mapped to a primary key may not be called. The cmr-field is read-only.

Please suggest a way for us to set the SENDER_SECURITY_ID and RECEIVER_SECURITY_ID when we create the entity bean for TREG_CPP_DOC_EXCHANGE.
[ March 07, 2005: Message edited by: maven nascent ]