CODEWITHSUNDEEP
javahibernatejpaormhibernate-mapping
MortalFool
MortalFool

Reputation: 1101

JPA Embeddeble PK and nullable field

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!

Upvotes: 3

Views: 703

Answers (4)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153850

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

  1. Add a surrogate primary key.

  2. 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);

Upvotes: 3

Karibasappa G C
Karibasappa G C

Reputation: 2732

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;

Upvotes: 0

Grim
Grim

Reputation: 2026

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.

Upvotes: 0

nazlo
nazlo

Reputation: 431

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

Upvotes: 1

Related Questions