• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Part of foreign key in primary key

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Been there. Done that. Went back for more. But this time, I took this tiny ad with me:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic