Primary Keys that are also Foreign Keys

[juniorcarl]

Hello,

I have the following tables:

Student - PK is student_id
Course -PK is course_id
Student_Course - PK is student_id and course_id

I needed some additional fields in the association table, so I created an entity bean for Student_Course with student_id and course_id as CMR fields.

As far as I know, PK is set in ejbCreate and CMR is set in ejbPostCreate. In this case, the PKs are also CMR fields...so how do I set them?

I can't think of a way to set it in ejbCreate. I did try setting it in ejbPostCreate but I got the NULL Primary Key exception, as expected. I don't want to have to create a separate PK field in the association table.

Many thanks.

[aloubyansky]

A field is eaither CMP or CMR, it can't be both.
You don't have to create Student_Course EJB at all. You can just map keys to Student_Course. See online docs for an example.

[juniorcarl]

I created the Student_Course EJB because I need fields other than student_id and course_id. For instance, I need another field that stores the date when the student registers for the course, etc.

If I didn't create the Student_Course ejb, I wouldn't be able to store the additonal fields as the association table would only take the student_id and course_id.

[aloubyansky]

Then create Student-Student_Course and Course-Student_Course relationships and map foreign key fields to primary key columns in Student_Course. And there should not be direct relationships between Course and Student.

[juniorcarl]

That's exactly what I did.

I'm using the compound PK (student_id, course_id) in the Student_Course EJB to ensure that no student registers for the same course more than once.

Therein lies the problem. As you said earlier, a field can only be either CMP or CMR. But the student_id and course_id fields in the Student_Course tables are CMR fields. How do I then set these CMR fields?

[aloubyansky]

CMP fields:
studentId - maps to -> student_id
courseId - maps to -> course_id

CMR fields:
student - maps to -> student_id
course - maps to -> course_id

[juniorcarl]

Thanks Alex. Following was what I did and it seemed to work. But doesn't this mean that both the fields are now being used as CMP as well as CMR fields which is not encouraged? Either that or my understanding of CMP/CMR fields is wrong...

public abstract class StudentCourseBean implements EntityBean {

public abstract String getStudentId();
public abstract void setStudentId(String studentId);

public abstract String getCourseId();
public abstract void setCourseId(String courseId);

public abstract StudentLocal getStudent();
public abstract void setStudent(StudentLocal student);

public abstract CourseLocal getCourse();
public abstract void setCourse(CourseLocal course);

...

public StudentCoursePK ejbCreate(String studentId, String courseId, ...) {
setStudentId(studentId);
setCourseId(courseId);
...
return null;
}

public void ejbPostCreate(String studentId, String courseId, ...) {
}

...
}