I'm having some problems when hibernate is deploying my application.
I 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:
(id_office_o, id_period), but the foreign key was defined as (id_period, id_office_o) in the table and in the mapping.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
Thanks everyone.
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 environment.