Mapping problems with overlapping composite foreign keys

nhajratw · **Joined:** Sat Dec 20, 2003 10:48 am **Posts:** 2

Folks, we are having some serious mapping issues with an existing database, so while some minor DB modification may be possible, it should be avoided.

I believe the crux of the problem is because the BRAND column is shared between the primary key of BMP_PROGRAM and it's foreign key to BMP_PROGRAM_GROUP.

Questions come up in where we should be putting CascadeType=All, insertable=false, updatable=false, etc.

Please take a look and offer advice... How would you set up the mappings properly?

Hibernate version: 3.2.2 + EntityManager + Annotations + Spring 2.0

Name and version of the database you are using: Oracle 10g

Tables:

Code:

/* **************************************
** BMP_BRAND
** *********************************** */
CREATE TABLE BMP_BRAND (
       BRD_BRAND            VARCHAR2(8)             NOT NULL,
       BRD_NAME             VARCHAR2(80)            NOT NULL,
);

ALTER TABLE BMP_BRAND ADD (
  CONSTRAINT BMP_BRAND_PK PRIMARY KEY (BRD_BRAND));


/* **************************************
** BMP_PROGRAM_GROUP
** *********************************** */
CREATE TABLE BMP_PROGRAM_GROUP (
       BPG_BRAND            VARCHAR2(8)             NOT NULL,
       BPG_PROG_GROUP       VARCHAR2(8)             NOT NULL,
       BPG_DESC             VARCHAR2(80)            NOT NULL,
)


ALTER TABLE BMP_PROGRAM_GROUP ADD (
  CONSTRAINT BMP_PROGRAM_GROUP_PK PRIMARY KEY (BPG_BRAND,BPG_PROG_GROUP));

ALTER TABLE BMP_PROGRAM_GROUP ADD (
  CONSTRAINT BPG_BRAND_FK FOREIGN KEY (BPG_BRAND)
    REFERENCES BMP_BRAND (BRD_BRAND));


/* **************************************
** BMP_PROGRAM
** *********************************** */
CREATE TABLE BMP_PROGRAM (
       BPR_BRAND            VARCHAR2(8)             NOT NULL,
       BPR_PROGRAM          VARCHAR2(8)             NOT NULL,
       BPR_PROG_GROUP       VARCHAR2(8)             NOT NULL,
       BPR_DESC             VARCHAR2(80)            NOT NULL,
)


ALTER TABLE BMP_PROGRAM ADD (
  CONSTRAINT BMP_PROGRAM_PK PRIMARY KEY (BPR_BRAND,BPR_PROGRAM));

ALTER TABLE BMP_PROGRAM ADD (
  CONSTRAINT BPR_PROG_GROUP_FK FOREIGN KEY (BPR_BRAND,BPR_PROG_GROUP)
    REFERENCES BMP_PROGRAM_GROUP (BPG_BRAND,BPG_PROG_GROUP));

Domain Objects

Code:

/****************************
 * BRAND Domain Object 
 ****************************/
@Entity
@Table(name = "BMP_BRAND")
public class Brand {


   @Id
   @Column(name = "BRD_BRAND")
   private String id;

   @Column(name = "BRD_NAME")
   private String name;

   public String getName() {
      return name;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getId() {
      return id;
   }
   public void setId(String code) {
      this.id = code;
   }
}


/****************************
 * PROGRAM GROUP Domain Object 
 ****************************/
@Entity
@Table(name = "BMP_PROGRAM_GROUP")
public class ProgramGroup {

   @Id
   private ProgramGroupPk id;

   @Column(name = "BPG_DESC")
   private String description;


   public String getDescription() {
      return description;
   }
   public void setDescription(String description) {
      this.description = description;
   }
   public ProgramGroupPk getId() {
      return id;
   }
   public void setId(ProgramGroupPk id) {
      this.id = id;
   }

}


/****************************
 * PROGRAM GROUP Primary Key 
 ****************************/
@Embeddable
public class ProgramGroupPk {

   @ManyToOne
   @JoinColumn(name = "BPG_BRAND", referencedColumnName = "BRD_BRAND")
   private Brand brand;

   @Column(name = "BPG_PROG_GROUP")
   private String programGroupId;

   public Brand getBrand() {
      return brand;
   }
   public void setBrand(Brand brand) {
      this.brand = brand;
   }
   public String getProgramGroupId() {
      return programGroupId;
   }
   public void setProgramGroupId(String programGroupId) {
      this.programGroupId = programGroupId;
   }
}


/****************************
 * PROGRAM Domain Object 
 ****************************/
@Entity
@Table(name = "BMP_PROGRAM")
public class Program {

   @EmbeddedId
   private ProgramPk id;

   @ManyToOne
   @JoinColumns( {
         @JoinColumn(name = "BPR_BRAND", referencedColumnName = "BPG_BRAND", insertable = false, updatable = false),
         @JoinColumn(name = "BPR_PROG_GROUP", referencedColumnName = "BPG_PROG_GROUP", insertable = false, updatable = false) })
   private ProgramGroup group;
   

   @Column(name = "BPR_DESC")
   private String description;


   public String getDescription() {
      return description;
   }
   public void setDescription(String description) {
      this.description = description;
   }
   public ProgramPk getId() {
      return id;
   }
   public void setId(ProgramPk id) {
      this.id = id;
   }
   public ProgramGroup getGroup() {
      return group;
   }
   public void setGroup(ProgramGroup group) {
      this.group = group;
   }
}

/****************************
 * PROGRAM Primary Key 
 ****************************/
@Embeddable
public class ProgramPk extends {

   @ManyToOne
   @JoinColumn(name = "BPR_BRAND", referencedColumnName = "BRD_BRAND")
   private Brand brand;

   @Column(name = "BPR_PROGRAM")
   private String programId;

   public String getProgramId() {
      return programId;
   }
   public void setProgramId(String campaignId) {
      this.programId = campaignId;
   }
   public Brand getBrand() {
      return brand;
   }
   public void setBrand(Brand brand) {
      this.brand = brand;
   }

}