I'm developing a filing system where I have 3 tables. PROJECTS table consists of projectid, project name and other details (see below). This is an existing class and populated schema and I do not want to modify this part of the application if possible.
Folders table (Called ProjectClassification) consists of folderid and foldername and is the owning side of a unidirectional onetomany relationship.
Project_Folders is a join table. I'm using JPA 2.0 (EclipseLink) and JSF 2.0 as my web framework.
My basic problem is I can't add duplicate records to the join table using a merge operation. MERGE is good for adding records until the owning key already exists, after which point it will only update the join table. I know this is the way it's supposed to work but I need to add new records even if there's a duplicate of the owning key. This will allow me to store different projects in the same folder.
The folder entity class:
The Project entity class is a pre existing code and I do not want to modify at all if possible:
I then use two html select lists to select values for projectid and proclassid which call the following methoid using a JSF managed bean:
My questions are:
1) Is MERGE the correct operation used to add records into a join table?
2) Is there a way to add records that contain duplicate keys (foreign keys represented as new records in the join table) using MERGE?