0

I have the following tables:

EMPLOYEE
---------
EMPL_ID (PK)
FRST_NAME
LAST_NAME
EMP_TYP_CODE
(etc.)

CODE
---------
DOMAIN (PK part)
CODE (PK part)
DESCRPTN

Because I want to have a single code lookup table with codes partitioned by domain rather than a separate lookup table for each code, which would introduce many tables in my schema and I don't like that. So the PK in CODE is composite (DOMAIN, CODE). Imagine that the data in CODES is something like

CODE_DOMAIN  CODE   DESCRPTN
--------------------------------
EMP_TYP      F      full time
EMP_TYP      P      part time
EMP_TYP      T      temp
COMP_LEVEL   A      entry
COMP_LEVEL   B      mid
(etc.)

When linking my Employee Entity class to the Code entity class, how do I specify that what I am interested in the CODE table is only its subset that have the DOMAIN of EMP_TYP without having a redundant DOMAIN column/field in the Employee table/class?

I tried 

@ManyToOne
@JoinColumn(name = "EMP_TYP_CODE") 
private Code empType;

but got

org.hibernate.AnnotationException: A Foreign key refering com.myapp.Code from com.myapp.Employee has the wrong number of column. should be 2.

Please also see the sequel to this question.

Community
  • 1
  • 1
amphibient
  • 29,770
  • 54
  • 146
  • 240
  • I am not sure what you are trying to achieve is "legal" SQL. Not an expert, but I think different DB provider (`MySQL`, `PostgreSQL`, `MSSQL`, etc) may have different policy and let you or not doing this. Read this article: http://searchoracle.techtarget.com/answer/Foreign-key-referencing-only-part-of-composite-primary-key (it asks for subscription). When using JPA, always keep in mind that if you want to perform a task considered bad practice, JPA will stumble on your way and make your goal hard to reach (I fell back on native query when I had to update a foreign key for instance) – ThanksForAllTheFish Jan 07 '15 at 18:46
  • what i am asking is, can i hard code the `DOMAIN` in my `Employee` class before joining to the `Code` class. I could have a `DOMAIN` field in the Employee table but it would be redundant as it would always be the same. In that case, I could do something like `@JoinColumns({@JoinColumn(name = "DOMAIN", referencedColumnName = "DOMAIN"), @JoinColumn(name = "EMP_TYPE_CODE", referencedColumnName = "CODE")})` – amphibient Jan 07 '15 at 23:35

0 Answers0