JPA Embeddeble PK and nullable field

Question

I have a table called "Attributes" which has a PK of 3 fields which can be null. In this case Style_no is not null but item_no and size_no are null.

Is it possible to have a Embeddeble PK where fields can be null?

@Entity
@Table(name="ATTRIBUTE")
public class Attribute {

    @EmbeddedId
    private AttributePK attrPK;
    ...

    @Embeddable
    public static class AttributePK implements Serializable{

        private static final long serialVersionUID = -2976341677484364274L;

        @Column(name="STYLE_NO", nullable=true)
        protected String styleNo;

        @Column(name="ITEM_NO", nullable=true)
        protected String itemNo;

        @Column(name="SIZE_NO", nullable=true)
        protected String sizeNo;
    ...

When i try to reference over one field e.g. style_no the result amount is 0.

@OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="attrPK.styleNo")
@MapKey(name="attrPK.name")
public Map<String,Attribute> attributesX;

OR

@OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL, orphanRemoval=true)
@JoinColumn(name="STYLE_NO", referencedColumnName="STYLE_NO")
private List<Attribute> attributes;

When i remove item_no and size_no as pk im receiving a valid result.

Edit: To make my question more specific. Is per JPA guideline or "common sense" not allowed to use nullable fields for EmbeddebedId? If not, what annotions or logic do i need to add to make it work without adding another PK? Once filling the nullable field in the PK with values. The result is corrct.

Thank you very much!

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

Since you must not use null in your PK, this is what you should do:

Add a surrogate primary key.
You can still achieve the uniqueness constraint and the default PK index with a parial index (in PostgreSQL):

CREATE UNIQUE INDEX style_item_size_idx ON my_table (Style_no, item_no, size_no) WHERE (item_no IS NOT NULL AND size_no IS NOT NULL);

edited May 23 '17 at 12:31

Community

1
1

answered May 27 '14 at 11:17

Vlad Mihalcea

142,745
71
566
911

Thank you but, I was explicit asking a solution wihtout adding another id - "..it work without adding another PK..". – MortalFool May 29 '14 at 13:35
I understand, only that this is a DB limitation you need to be aware of. – Vlad Mihalcea May 29 '14 at 13:37
Sure, my exmaple above is just to find out if Hibernate, as an extra layer, can avoid such DB specific borders. No real use case. – MortalFool May 29 '14 at 13:52
1

I always choose Hibernate for [these reasons](http://stackoverflow.com/questions/23930151/is-it-a-good-idea-to-use-hibernate-for-representing-table-relations/23932324#23932324), otherwise I would use only [JOOQ](http://www.jooq.org/doc/3.3/manual/sql-building/sql-statements/select-statement/window-clause/) which has great querying abilities. – Vlad Mihalcea May 29 '14 at 13:57

score 1 · Accepted Answer · edited May 23 '17 at 12:16

1

Take a look here Seems, that answer that (NULL == NULL) -> false is about your question.

edited May 23 '17 at 12:16

Community

1
1

answered May 26 '14 at 10:33

nazlo

431
3
9

Thank you, for the link. But as the comments below mentioned correctly its not that satisfing. E.g. from JPA it not allowed to use an Id on a field which can be null. But I cannot find the same restriction for EmbeddedId. All i need is if there is a workaround without adding another artificial Id or a link to a source which says that nullable field in a EmbeddedId are not allowed. – MortalFool May 26 '14 at 11:03

Grim · Answer 3 · 2014-05-26T18:14:56.480

0

Usually a compound pk is not allowed to have null-values in the database.

Technically we would say: Why not, a null can be a value too.

The DB-Analyst would ask: Ok, if i sort 1,2,3,4,null,5. Would you say null is before 1 or after 5?

Therefore PostgreSQL, Oracle, MySQL will not support null-values in compound primary keys.

edited May 26 '14 at 18:14

answered May 26 '14 at 14:38

Grim

1,938
10
56
123

1

1) For the order i would say that the DB can decide it on its own. Technically its just important that it is defined anywhere. 2) Yes, a PK contraint is not allowed. For a UNIQUE constrain it is allowed but it still doesnt work with hibernate. – MortalFool May 26 '14 at 16:53
@MortalFool you are right, i added the 2. word from last: `primary` to clear the difference between keys and primarykeys. – Grim May 26 '14 at 18:16

score 0 · Answer 4 · answered May 26 '14 at 18:23

0

JPA guideline or "common sense" not allowed to use nullable fields for EmbeddebedId? If not, what annotions or logic do i need to add to make it work without adding another PK?

answer is as below

@EmbeddedId
private AttributePK attrPK;

does not allow the primary key to be null.

so to make it happen use another annotation like below

@IdClass(AttributePK.class)
 private AttributePK attrPK;

answered May 26 '14 at 18:23

Karibasappa G C

2,686
1
18
27

Thank you, ill try to validate it tomorrow. – MortalFool May 29 '14 at 13:36
The IdClass has an `@Target({ElementType.TYPE})`, how can you put it on a field??? – LeOn - Han Li Apr 21 '16 at 21:06

JPA Embeddeble PK and nullable field

4 Answers4