GeeCON Prague 2014*
The moose likes Object Relational Mapping and the fly likes Newbie Question: Referential Integrity and Cascading Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Newbie Question: Referential Integrity and Cascading" Watch "Newbie Question: Referential Integrity and Cascading" New topic
Author

Newbie Question: Referential Integrity and Cascading

Perry Terrance
Ranch Hand

Joined: Oct 02, 2009
Posts: 55
I have a very simple 3-table relationship STUDENT<->SCHOOL ACTIVITIES<->SCHOOL OFFICIALS.

SCHOOL ACTIVITIES is many-to-one to both STUDENT and SCHOOL OFFICIALS.
SCHOOL ACTIVITIES is bi-directional both ways.

Therefore SCHOOL ACTIVITIES is the sole linking-table between the STUDENT and SCHOOL OFFICIALS table.

I've been using JPA2 and correctly generated all 3 entities relationships:

STUDENT @OneToMany <->SCHOOL ACTIVITIES;
SCHOOL OFFICIALS @OneToMany <-> SCHOOL ACTIVITIES;
SCHOOL ACTIVITIES @ManyToOne <-> STUDENT;
SCHOOL ACTIVITIES @ManyToOne <-> SCHOOL OFFICIALS;


I can read each entity entry if I wanted to, but I am now trying to do an insert that adds a new entry on all 3 tables at once.

I have CascadeType.ALL on all the annotated areas of @ManyToOne and @OneToMany in these 3 entity classes. But when I try do all-3-in-one insert - say - taking a Student Object (and its encapsulated school activities and school officials value) and persist it - I keep on getting a Referential Integrity error on the Insert for SCHOOL ACTIVITIES (which is the sole-table linking SCHOOL and SCHOOL OFFICIALS). I assumed that because I did a CascadeType.ALL on all 3 entities, it doesn't really matter which object I actually persist - since it will go to all of them eventually...

So from my understanding, the 2 FKs in SCHOOL ACTIVITIES has to point to already-existing PKs in both STUDENT and SCHOOL OFFICIALS. I'm still new to ORM-technologies in general, so I wonder if JPA is "smart" enough to know that it first needs to populate the corresponding STUDENT and SCHOOL OFFICIALS entry before adding the SCHOOL ACTIVITIES entry (which corresponds to the STUDENT and SCHOOL OFFICIALS entry)?
Ran Pleasant
Ranch Hand

Joined: Jan 16, 2003
Posts: 75
Perry

I must respectfully disagree with your design. The student, the school official, and the school activity should all be existing records. What you want to do is to connect the student to an school activity. Thus, you need a fouth entity that reflects registration in the activity. I would also suggest that the school official probably does not need a list of his activities, these can be pulled by query when they are needed. Likewise with the student. Circlar relationships can be a source of major problems. For instance in your design, you might read one student resulting in a pull of all of his activities, which pulls the officials, which pulls all of their activities, which......... Object relations is one of those areas where you "shouldn't just because you can".


Ran
Perry Terrance
Ranch Hand

Joined: Oct 02, 2009
Posts: 55
Ran Pleasant wrote:Circlar relationships can be a source of major problems.


Actually this relationship is not circular/cyclic - since there is no direct-connection between STUDENT and SCHOOL OFFICIALS. I will need to stick with this design though because it was designated that a table of just SCHOOL ACTIVITIES will be needed.

In terms of coding JPA2 though, it should still work right to do the Insert coding as I mentioned above?
Ran Pleasant
Ranch Hand

Joined: Jan 16, 2003
Posts: 75
Perry Terrance wrote:Actually this relationship is not circular/cyclic - since there is no direct-connection between STUDENT and SCHOOL OFFICIALS.

I was actually referring to the relationship between Activity and Student and between Official and Activity. Unless you use lazy loading you can easily pull a lot of data.

I will need to stick with this design though because it was designated that a table of just SCHOOL ACTIVITIES will be needed.

I do agree that SCHOOL ACTIVITIES is needed, I was just suggesting that one more table is needed. However, if you are force to go with the tables you have then all I can say is that I know that game well. I cannot count the times over the last 20 years that I was told my reall nice object model would not workd because I had to use the existing tables. It's just part of the profession, but if it was easy they wouldn't need you.

In terms of coding JPA2 though, it should still work right to do the Insert coding as I mentioned above?

Well, give it a try and let us know the results of your testing.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Most JPA providers should maintain referential integrity constraints correctly. But some may not, I don't think the JPA spec says much about this.

What provider are you using and what error do you get?

You may need to use a flush() to avoid the errors, or remove or defer your constraints if your JPA provider is not support constraints correctly. You may also have mapped things incorrectly, so maybe include your mapping.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Sean Clark
Rancher

Joined: Jul 15, 2009
Posts: 377

So according to your schema, you are saying that each activity will only have one student and one school official associated to it.
Surely a school activity will have more than one student associated to it, unless it is different from any other school activities I've seen.
More likely this would be a many-to-many where one student has many activites and each activity has many students associated with it.

You could also say the same for school officials, however it might be more reasonable to say that only one can map to an activity.

Just my opinion, obviously if you have to keep the schema, then there is not much you can do, although it will fail normalisation due to the large amount of duplicate data that you will have in the school activity table.

Sean


I love this place!
Perry Terrance
Ranch Hand

Joined: Oct 02, 2009
Posts: 55
Yes, I am forced to use this table schema.

I am using Hibernate as the JPA2-Provider.

Now for SCHOOL ACTIVITIES, I do NOT have a specified GETTER/SETTER for the StudentId or the SchoolOfficialsId in the EJB 3.0 Entity of SchoolActivities. Instead I have the GETTER/SETTER for Student and SchoolOfficials. I DO have the GETTER/SETTER for the StudentId on Student and the GETTER/SETTER of the SchoolOfficialsId on the SchoolOfficials EJB 3.0 Entities though. Overall I think this setup is correct due to the STUDENT<->SCHOOL ACTIVITIES<->SCHOOL OFFICIALS relationship.

I am using List<SchoolActivities> schoolActivitiesList as my GETTERS/SETTERS between STUDENT<->SCHOOLACTIVITIES and SCHOOLOFFICIALS<->SCHOOLACTIVITIES.

For the Java-code logic I used to add a single-entry to all 3 correlated-tables, I did a .set on ALL the relevant field-values for all 3 tables with .set encapsulation. In addition, for the LIST fields (which correlates to the STUDENT<->SCHOOLACTIVITIES and SCHOOLOFFICIALS<->SCHOOLACTIVITIES relationships), I first .set all the fields in a SchoolActivities entity instance and then I encapsulated that specific SchoolActivities entity by doing something like this:

For Student entity


For SchoolOfficials entity
 
GeeCON Prague 2014
 
subject: Newbie Question: Referential Integrity and Cascading