Why I can't I use JPA to update a row with a value for a string with a length of 3, if the column type in an Oracle database table is CHAR(4 BYTE)?

Question

I have an entity class with a composite primary key that looks this:

@Entity
@Table(schema = "ANYBODY", name = "PERSON")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {
    @EmbeddidId
    private PersonPK personPk;

    @Column(name = "FIRST_NAME")
    private firstName;

    @Column(name = "LAST_NAME")
    @Column lastName;
}

... and the composite primary key class:

@Embeddable
@Data
@NoArgsConstructor
public class PersonPK implments Serializable {
    @NotNull
    @Column(columnDefinition = "CHAR(4 BYTE)", name = "NICK_NAME")
    private String nickname;

    @NotNull
    @Column(name = "AGE")
    private Integer age;
}

My Oracle database schema looks like this:

CREATE TABLE ANYBODY.PERSON
(
  FIRST_NAME VARCHAR2(30 BYTE) NOT NULL
, LAST_NAME VARCHAR2(30 BYTE) NOT NULL
, AGE NUMBER NOT NULL
, NICK_NAME CHAR(4 BYTE) NOT NULL
);

ALTER TABLE ANYBODY.PERSON ADD CONSTRAINT PERSON PRIMARY KEY
(
  AGE
, NICK_NAME
);

I've found that I can use the JPA save() method to create a new row with an entity that looks like this: new Person(new PersonPK("Bud", 40), "Nathan", "Reed") However, if I try to use save() to update the row with: new Person(new PersonPK("Bud", 40), "Nate", "Reed"), I get a org.hibernate.exception.ConstraintViolationException; java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (ANYBODY.PERSON) violated

If I create a new row using JPA save() with an entity that looks like this: new Person(new PersonPK("Lily", 40), "Lilia", "Millar"), (notice the nickname property on the primary key now has a string value of length 4), I can use JPA save() to update the row in the Oracle database without any issues. I think it has something to do with the fact that I've defined the column with a type of CHAR(4 BYTE), but I don't understand why I can use JPA save() to create a row with a value for the NICK_NAME column that has a string of length 3, but I can't use JPA save() to update that row.

So. Why I can't I use JPA save() to update a row with a value for a string that has a length of 3, if the column type of a table in an Oracle database is CHAR(4 BYTE)?

Answer 1

You are getting error because of Unique Constraint being broken

So, this data already exists new Person(new PersonPK("Bud", 40), "Nathan", "Reed")

You must already bbe having an entry for

Nickname: Bud

Age: 40

It is not the 4 byte issue

Try clear the table, then make the entry to confirm the same

Answer 2

Characters store a fixed length value of 1. All you're specifying is the length shown. Store as a String (TEXT, LONGTEXT, VARCHAR), not a character.

@Column(columnDefinition = "VARCHAR(4)", name = "NICK_NAME")

Answer 3

You have created the PK with AGE and NICK_NAME.

This means that when you add a new record, the combination between age and nick_name should be unique from other previous records. So probably you have another ("Bud", 40) already on your table that is causing this error.

Solutions:

• Try deleting the row ("Bud", 40) in table
• Change the nick name or age.
• Suggestion: Age and name doesn't really combine in this case, since you might have a lot of persons with same name and age. So try and making another combination for your PK