isccarrasco isccarrasco - 4 years ago 181
Java Question

How to define order of fields on foreign key mapping using Hibernate and JPA?

I'm having some problems when hibernate is deploying my application.

I'm see some errors on the log that say hibernate can't update tables because found foreign keys violations, like this:

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "order" violates foreign key constraint "fk1w50d63tw42cwgrb7pjn11crs"
Detail: Key (id_office_o, id_period)=(2, 1) is not present in table "office".


But on my table definition i have the FK
fk_order_office_o
as a
(id_period, id_office_o)
relation.

Here is my tables definitions.

CREATE TABLE period (
id_period INTEGER PRIMARY KEY NOT NULL,
initial_date TIMESTAMP,
final_date TIMESTAMP
);

CREATE TABLE office (
id_period INTEGER NOT NULL,
id_office INTEGER NOT NULL,
large_name VARCHAR(50) NOT NULL,
code_name VARCHAR(2) NOT NULL,
CONSTRAINT pk_office PRIMARY KEY (id_periodo, id_office),
CONSTRAINT fk_office_period FOREIGN KEY (id_period) REFERENCES period (id_period)
);
CREATE UNIQUE INDEX uk_office_id_office ON office (id_office);

CREATE TABLE order (
id_order INTEGER PRIMARY KEY NOT NULL,
id_period INTEGER,
id_office_o INTEGER,
id_office_d INTEGER,
status INTEGER,
CONSTRAINT fk_order_office_o
FOREIGN KEY (id_period, id_office_o)
REFERENCES office (id_period, id_office)
MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_order_office_d
FOREIGN KEY (id_period, id_office_d)
REFERENCES office (id_period, id_office),
MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_order_period
FOREIGN KEY (id_period)
REFERENCES period (id_period)
MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
);


And these is the data on my tables.

Period.
+------------+--------------+------------+
| id_period | initial_date | final_date |
|------------|--------------|------------+
| 1 | 2016-01-01 | 2017-12-31 |
+------------+--------------+------------+

Office.
+------------+-----------+------------+-----------+
| id_period | id_office | large_name | code_name |
|------------|-----------|------------|-----------|
| 1 | 1 | Sales | SL |
| 1 | 2 | Billing | BL |
| 1 | 3 | Shipments | SH |
| 1 | 4 | Returns | RT |
+------------+-----------+------------+-----------+

Order.
+------------+-----------+--------------+--------------+-----------+
| id_order | id_period | id_office_o | id_office_d | status |
+------------+-----------+--------------+--------------+-----------+
| 1 | 1 | 2 | 3 | 1 |
| 2 | 1 | 2 | 3 | 1 |
| 1 | 1 | 3 | 4 | 1 |
| 1 | 1 | 1 | 2 | 1 |
+------------+-----------+--------------+--------------+-----------+


As you can see, the FK of the
office
table that related with the order table have a CONSTRAINT named
fk_order_office_o
that use the
id_period
and
id_office
, in thats order.

The entity mappings is here:

@Embeddable
public class OfficePK implements Serializable {

@Column(name = "id_period")
private Integer idPeriod;

@Column(name="id_office")
private Integer idOffice;

public OfficePK() {
}

public OfficePK(Integer idPeriod, Integer idOffice) {
this.idPeriod = idPeriod;
this.idOffice = idOffice;
}

...

}


@Entity
@Table(schema="enterprise", name="office")
public class Office {


@EmbeddedId
private OfficePK idOffice;

private String largeName;
private String codeName;

public Office() {
}

public OfficePK getIdOffice() {
return this.idOffice;
}

public void setIdOffice(OfficePK idOffice) {
this.idOffice = idOffice;
}

...

}


@Entity
@Table(schema="operation", name = "order")
public class Order {

@Column(name="id_order")
private Integer idOrder;

@ManyToOne
@JoinColumns(
value = {
@JoinColumn(name="id_period",
referencedColumnName = "id_period",
nullable = false, insertable = false,
updatable = false),
@JoinColumn(name="id_office_o",
referencedColumnName = "id_office",
nullable = false, insertable = false,
updatable = false)
},
foreignKey = @ForeignKey(name = "fk_order_office_o")
)
private Office officeO;

@ManyToOne
@JoinColumns(
value = {
@JoinColumn(name="id_period",
referencedColumnName = "id_period",
nullable = false, insertable = false,
updatable = false),
@JoinColumn(name="id_office_d",
referencedColumnName = "id_office",
nullable = false, insertable = false,
updatable = false)
},
foreignKey = @ForeignKey(name = "fk_order_office_d")
)
private Office officeD;

public Order() {
}

...

}


When the application is deploying, i see some errors that call my atention:


  1. I don't define the foreign key in the order thats show on log file, the log file are looking the pair for
    (id_office_o, id_period)
    , but the foreign key was defined as
    (id_period, id_office_o)
    in the table and in the mapping.

  2. I'm setting the name of the foreign key, but hibernate are setting a random name like
    fk1w50d63tw42cwgrb7pjn11crs



How can define the order of the fields on the foreign key mapping of the entities?, it is possible?.. or how should define or mapping the fields for the foreign key?

I'm using


  1. Hibernate 5.2.6.Final

  2. Spring-JPA 1.10.6.RELEASE



Thanks everyone.

Answer Source

The part related to the @ForeignKey name might be related to HHH-11180.

The part about the foreign key order, you can solve it by providing a custom org.hibernate.boot.model.naming.ImplicitNamingStrategy implementation, which extends from the default org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl and overrides the determineForeignKeyName method so that it generates the Foreign Key with the columns in the order that you need.

Now, all this effort you're going through is a smell that you're not managing the schema properly. You should never use the automatic HBM2DDL schema generation in production. You should use Flyway instead. That's the root problem in your environement.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download