File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Part of foreign key in primary key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Part of foreign key in primary key" Watch "Part of foreign key in primary key" New topic
Author

Part of foreign key in primary key

maven nascent
Greenhorn

Joined: Mar 07, 2005
Posts: 1
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 ]
 
 
subject: Part of foreign key in primary key