6 Replies Latest reply on Aug 10, 2004 3:56 AM by Junior Carl

    Primary Keys that are also Foreign Keys

    Junior Carl Newbie

      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.

        • 1. Re: Primary Keys that are also Foreign Keys
          Alexey Loubyansky Master

          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.

          • 2. Re: Primary Keys that are also Foreign Keys
            Junior Carl Newbie

            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.

            • 3. Re: Primary Keys that are also Foreign Keys
              Alexey Loubyansky Master

              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.

              • 4. Re: Primary Keys that are also Foreign Keys
                Junior Carl Newbie

                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?

                • 5. Re: Primary Keys that are also Foreign Keys
                  Alexey Loubyansky Master

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

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

                  • 6. Re: Primary Keys that are also Foreign Keys
                    Junior Carl Newbie

                    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, ...) {
                    }

                    ...
                    }