I have two entities as following, when I try to add items to my car table it shows following error message;therefore, it does not allow me to have more than one car with 'Auto' transmission.
Error:
#1062 - Duplicate entry 'Auto' for key 'UK_bca5dfkfd4fjdhfh4ddirfhdhesr'
Entities:
Car
@Entity
public class Car implements java.io.Serializable {
@Id
@GeneratedValue
long id;
@Column(name="transmission", nullable = false)
String transmission;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
Set<CarFactory> factories;
...
}
Sample values for car table:
10 Auto
12 Auto
43 Manual
54 Manual
65 Normal
68 Standard
90 Normal
99 NoGear
CarFactory
@Entity
public class CarFactory implements java.io.Serializable {
@Id
@JoinColumn(name="transmission",referencedColumnName = "transmission")
@ManyToOne
Car car;
@Id
@JoinColumn(name="factory_id", referencedColumnName= "id")
@ManyToOne
Factory factory;
...
}
Expected values for CarFactory table
Auto Fac1
Auto Fac2
Manual Fac1
Auto Fac5
Standard Fac6
Normal Fac3
NoGear Fac1
Ive followed answer of this question as well but it did not work.
Long story short, I need to have a table with two foreign keys from other tables, with combined primary key. It should not force unique foreign key in participating tables.
I emulated your use case and you can find the test on GitHub.
These are the mappings:
@Entity(name = "Car")
public static class Car implements Serializable {
@Id
@GeneratedValue
long id;
@Column(name="transmission", nullable = false)
String transmission;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
Set<CarFactory> factories;
}
@Entity(name = "Factory")
public static class Factory implements Serializable {
@Id
@GeneratedValue
long id;
}
@Entity(name = "CarFactory")
public static class CarFactory implements Serializable {
@Id
@ManyToOne
@JoinColumn(name = "transmission", referencedColumnName = "transmission")
Car car;
@ManyToOne
@Id
Factory factory;
public void setCar(Car car) {
this.car = car;
}
public void setFactory(Factory factory) {
this.factory = factory;
}
}
This is how you add some test data:
doInTransaction(session -> {
Car car = new Car();
car.transmission = "Auto";
Car car1 = new Car();
car1.transmission = "Manual";
Factory factory = new Factory();
session.persist(factory);
session.persist(car);
session.persist(car1);
CarFactory carFactory = new CarFactory();
carFactory.setCar(car);
carFactory.setFactory(factory);
CarFactory carFactory1 = new CarFactory();
carFactory1.setCar(car1);
carFactory1.setFactory(factory);
session.persist(carFactory);
session.persist(carFactory1);
});
And the test works just fine:
@Test
public void test() {
doInTransaction(session -> {
List<CarFactory> carFactoryList = session.createQuery("from CarFactory").list();
assertEquals(2, carFactoryList.size());
});
}
You get an exception because of the following unique constraint:
alter table Car add constraint UK_iufgc8so6uw3pnyih5s6lawiv unique (transmission)
This is the normal behaviour, since a FK must uniquely identify a PK row. Like you can't have more rows with the same PK, you can't have a FK identifier reference more than one row.
You mapping is the problem. You need to reference something else, not the transmision
. You need a unique Car identifier, like a VIN (Vehicle Identification Number), so your mapping becomes:
@Entity(name = "Car")
public static class Car implements Serializable {
@Id
@GeneratedValue
long id;
@Column(name="vin", nullable = false)
String vin;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
Set<CarFactory> factories;
}
@Entity(name = "CarFactory")
public static class CarFactory implements Serializable {
@Id
@ManyToOne
@JoinColumn(name = "vin", referencedColumnName = "vin")
Car car;
@ManyToOne
@Id
Factory factory;
public void setCar(Car car) {
this.car = car;
}
public void setFactory(Factory factory) {
this.factory = factory;
}
}
This way, the vin
is unique and the Child association can reference one and only one parent.