Unknown Error in ManyToOne Hibernate Mapping related to Foreign Composite Key on MySQL DB

Question

Im Trying to map Real_States:

CREATE TABLE REAL_STATES (
address VARCHAR(30) NOT NULL,
admin_id VARCHAR(15) NOT NULL,
real_state_type_id INT(6) NOT NULL,
block VARCHAR(3) NOT NULL,
internal_id INT(5) NOT NULL,
PRIMARY KEY (address, block, internal_id),
FOREIGN KEY (real_state_type_id) REFERENCES REAL_STATE_TYPES (real_state_type_id),
FOREIGN KEY (admin_id) REFERENCES ADMINS (admin_id)
);

which is related to Resident:

CREATE TABLE RESIDENTS (
resident_id VARCHAR(15) NOT NULL,
resident_name VARCHAR(20) NOT NULL,
resident_last_name VARCHAR(20) NOT NULL,
resident_phone_number VARCHAR(15) NOT NULL,
address VARCHAR(30) NOT NULL,
block VARCHAR(3) NOT NULL,
internal_id INT(5) NOT NULL,
PRIMARY KEY (resident_id),
FOREIGN KEY (address, block, internal_id) REFERENCES REAL_STATES (address, block, internal_id)
);

Through the entity for Real_State:

@Entity
@Table(name = "real_states")
public class RealState implements Serializable{
    private static final long serialVersionUID = 4033627448194380926L;
    @EmbeddedId
    private RealStateID realStateID;
    @Column(name = "real_state_type_id")
    @Enumerated(EnumType.ORDINAL)
    private RealStateEnum realStateEnum;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "admin_id", insertable = true)
    private Admin admin;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumns({@JoinColumn(name="address", referencedColumnName = "address", insertable = false, updatable = false),
        @JoinColumn(name="block",referencedColumnName = "block", insertable = false, updatable = false),
        @JoinColumn(name="internal_id",referencedColumnName = "internal_id", insertable = false, updatable = false)})
    private Resident resident;

with the embedded entity for the composite key:

@Embeddable
public class RealStateID  implements Serializable{
    private static final long serialVersionUID = 6485406412363395170L;
    @Column(name = "address")
    private String address;
    @Column(name = "block")
    private String block;
    @Column(name = "internal_id")
    private int internal_id;

and Resident entity:

@Entity
@Table(name = "residents")
public class Resident implements Serializable {
    private static final long serialVersionUID = -9052313410222202916L;
    @Id
    @Column(name = "resident_id")
    private String resident_id;
    @Column(name = "resident_name")
    private String resident_name;
    @Column(name = "resident_last_name")
    private String resident_last_name;
    @Column(name = "resident_phone_number")
    private String resident_phone_number;
    @Column(name = "address")
    private String address;
    @Column(name = "block")
    private String block;
    @Column(name = "internal_id")
    private int internal_id;
    @OneToMany(mappedBy = "resident", fetch= FetchType.EAGER)
    private List<RealState> realStates;

And for this specific case, im getting the next error:

22:18:32,825 WARN  [org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl] (ServerService Thread Pool -- 79) GenerationTarget encountered exception accepting command : Error executing DDL "alter table real_states add constraint FKeggvad6claf4gvemna1xdk2tm foreign key (address, block, internal_id) references residents (address, block, internal_id)" via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table real_states add constraint FKeggvad6claf4gvemna1xdk2tm foreign key (address, block, internal_id) references residents (address, block, internal_id)" via JDBC Statement
    at [e-mail]//org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at [e-mail]//org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
    at [e-mail]//org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
    at [e-mail]//org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433)
    at [e-mail]//org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249)
    at [e-mail]//org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
    at [e-mail]//org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
    at [e-mail]//org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at [e-mail]//org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310)
    at [e-mail]//org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
    at [e-mail]//org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1250)
    at [e-mail]//org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44)
    at [e-mail]//org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:170)
    at [e-mail]//org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:128)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at [e-mail]//org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:664)
    at [e-mail]//org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:213)
    at [e-mail]//org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at [e-mail]//org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1990)
    at [e-mail]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
    at [e-mail]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
    at java.base/java.lang.Thread.run(Thread.java:834)
    at [e-mail]//org.jboss.threads.JBossThread.run(JBossThread.java:513)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`real_state`.`#sql-3e28_118`, CONSTRAINT `FKeggvad6claf4gvemna1xdk2tm` FOREIGN KEY (`address`, `block`, `internal_id`) REFERENCES `residents` (`address`, `block`, `internal_id`))
    at com.mysql@8.0.22//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql@8.0.22//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql@8.0.22//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql@8.0.22//com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
    at com.mysql@8.0.22//com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at [e-mail]//org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:198)
    at [e-mail]//org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 22 more

before this i was getting an error which asked me to implement "insert should be false and update should be false" that's the reason why i've added those statements to every @JoinColumn annotation, Im really new at JPA, so I'd really appreciate any kind of advice or help that can guide me to the reason behind this problem.

Answer 1

if you are trying to save or update realstates then make sure that you are properly setting the resident object to realstate. The error log shows that there is foreign key violation while saving realstates which happens when the realstate object does not have resident object at the time of saving it.

Answer 2

According to your RESIDENTS table definition:

CREATE TABLE RESIDENTS (
  -- ...
  FOREIGN KEY (address, block, internal_id) REFERENCES REAL_STATES (address, block, internal_id)
);

the owner side of the Resident - RealState association should be defined in the Resident entity like below:

@Entity
@Table(name = "residents")
public class Resident implements Serializable {
   // ...

   @ManyToOne
   @JoinColumns({
      @JoinColumn(name="address", referencedColumnName = "address"),
      @JoinColumn(name="block", referencedColumnName = "block"),
      @JoinColumn(name="internal_id", referencedColumnName = "internal_id")
   })
   private RealState realState;
}

and then mappedBy side in the RealState entity.

@Entity
@Table(name = "real_states")
public class RealState implements Serializable {
   // ...
   @OneToMany(mappedBy = "realState", fetch= FetchType.EAGER, cascade = CascadeType.ALL)
   private List<Resident> resident;
}