-2

Is it possible to have both a composite key and a primary key in the same Domain Model (Entity Class) so that some tables (queries) are joined using the composite key and other tables (queries) are joined using the primary key?

I'm dealing with legacy applications and I have limited access to changing the underlying database. Some of our queries are expecting a single row result but are getting many rows because of flaws in our database design. We can fix this problem by introducing a composite key to one of our Domain Models but doing so will affect many (many) other components that rely on the original primary key.

From my understand of JPA and the reading I've done so far on this matter I do not think this is possible but I thought it would be worth a shot to reach out to others who may have had a similar problem.

Kyle Bridenstine
  • 6,055
  • 11
  • 62
  • 100
  • 1
    A combination of columns in a database table together forms a composite primary key, if necessary. As such, what is the isolated meaning of them as implied by, "*Is it possible to have both a composite key and a primary key in the same Domain Model*"? – Tiny Sep 11 '15 at 15:16
  • Yes. So if I create a composite key then all the joins will begin using that composite key. But, certain queries will break if I begin telling them to use the composite key. Those queries that will break need to somehow continue using the original primary key. We're using Hibernate with JPA so things are automatically joined by whatever the Domain Models key is (whether that's a single column primary key or a multi-column composite primary key). – Kyle Bridenstine Sep 11 '15 at 15:33

2 Answers2

0

The table has only one primary key, so you have no options to choose which primary key to use. Also, i can't understand why you going to have differences between database original model and JPA. Actually, getting single row instead of many rows is where clause's task.

You said some of your queries fails after adding composite pk, so may be you just made your composite pk in wrong way? Anyway, here is nice example or implementation composite pk, may be it will help you: Mapping ManyToMany with composite Primary key and Annotation:

Community
  • 1
  • 1
serhii
  • 1,135
  • 2
  • 12
  • 29
0

Maybe you should give a different look at your problem.

  1. If your queries are returning multiple and different rows, then you should be able to resolve this using a more specific WHERE clause;

  2. If your queries are returning multiple and equal rows, you should try the DISTINCT clause inside your query, example:

SELECT DISTINCT e FROM br.com.stackoverflow.Entity e

Bonifacio
  • 1,482
  • 10
  • 19