How to have a relationship based on a non-key field?

Question

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.

This is not a many-to-one association, since several cars have the same transmission. You should probably have a Set supportedTransmissions in Factory, and use a query to find all the factories that support the same transmission as a given car. — JB Nizet, May 02 '15 at 07:13
@JBNizet then which field would be the primary key of the table? I reckon it is common to have a table with composite key. In this way none of the primary keys is unique but their combination would be unique — Daniel Newtown, May 04 '15 at 03:27
When I try to add items to my **car** table it shows following error message - do you really need to add Auto multiletimes to you car table? It seems to me you only need to have 1 entry in your car table with transmission "Auto" and multiple such entries in your CarFactory table. — Kicsi, May 05 '15 at 08:02
Checkout also this question: http://stackoverflow.com/questions/7594812 It is very similar and was resolved using Hibernate's `@JoinFormula`. — dedek, May 05 '15 at 11:06
Why are you breaking Rules? what do you want actually?? @JoinColumn(name="transmission",referencedColumnName = "transmission") why do not you change this with @JoinColumn(name="car_id",referencedColumnName = "id") ??? — sgpalit, Jun 02 '15 at 14:45

Vlad Mihalcea · Accepted Answer · 2015-05-27T06:35:34.653

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());
    });
}

Update

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.

I am a bit confused with your explanation, let say I have Auto and Fac1 their combination will be unique so I wont have any other record like this;however, I may have Manual Fac1 or Normal Fac1. So why it might show different results? would you elaborate further please. — Daniel Newtown, May 05 '15 at 06:36
It is an association table which has only two fields both fields can have non-unique values but their combination must be unique. — Daniel Newtown, May 05 '15 at 06:41
Thanks, does that mean @JoinColumn should just be removed from factory field of CarFactory and that class should be static? Would you please explain further? Also whats your idea about chsdk's answer? — Daniel Newtown, May 08 '15 at 14:52
The class is static because I embedded it id a test class, but that's not your case. — Vlad Mihalcea, May 08 '15 at 15:34
Thanks but it still shows the same error message. I can not have two cars that both have the same transmissions. Lets say car id 1 Auto, car id 2 Auto — Daniel Newtown, May 26 '15 at 03:20
Replace the automatically generated schema with hbmddl with your own DB scripts to remove that unique constraint. — Vlad Mihalcea, May 26 '15 at 04:47
Michalcea I do not get you would you please elaborate further — Daniel Newtown, May 27 '15 at 01:13
This way you can control the columns that have a unique constraint and decide which ones should really be unique. — Vlad Mihalcea, May 27 '15 at 02:58
I read your blog and documentation of flyway, I should admit am so confused. Is there any easier way to do it? whats your idea about chsdk's answer? — Daniel Newtown, May 27 '15 at 03:59
I tried removing the foreign key on mysql but it returns errno: 150 — Daniel Newtown, May 27 '15 at 04:14
Alright, would you just confirm that your 'car' table accepts two records with the same transmission? lets say two records with 'manual' as value of their transmission. Question is updated thanks — Daniel Newtown, May 27 '15 at 06:04
Thanks, is not there any method to remove that unique constraint? I tried to do it on mysql but it returns errno:150 — Daniel Newtown, May 27 '15 at 06:40

score 7 · Answer 2 · edited May 23 '17 at 10:34

The problem here is that you are using a non primary key field as a foreign key which seems to be incorrect and your transmission field, should be unique, this line is incorrect:

@JoinColumn(name="transmission",referencedColumnName = "transmission")

You have a Many-To-Many mapping here which needs an @EmbeddedId property in the association table, and your code should be like this:

CarFactory class

@Entity
public class CarFactory {

   private CarFactoryId carFactoryId = new CarFactoryId();

   @EmbeddedId
   public CarFactoryId getCarFactoryId() {
       return carFactoryId;
   }

   public void setCarFactoryId(CarFactoryId carFactoryId) {
       this.carFactoryId = carFactoryId;
   }

   Car car;

   Factory factory;

   //getters and setters for car and factory
}

CarFactoryId class

@Embeddable
public class CarFactoryId implements Serializable{

    private static final long serialVersionUID = -7261887879839337877L;
    private Car car;
    private Factory factory;

    @ManyToOne
    public Car getCar() {
        return car;
    }
    public void setCar(Car car) {
        this.car = car;
    }

    @ManyToOne
    public Factory getFactory() {
        return factory;
    }
    public void setFactory(Factory factory) {
        this.factory = factory;
    }
    public CarFactoryId(Car car, Factory factory) {
        this.car = car;
        this.factory = factory;
    }
    public CarFactoryId() {}

}

Car class

@Entity
public class Car {

    @Id
    @GeneratedValue
    long id;
    @Column(name="transmission", nullable = false)
    String transmission;

    private Set<CarFactory> carFactories = new HashSet<CarFactory>();

    @OneToMany(mappedBy = "primaryKey.car",
    cascade = CascadeType.ALL)
    public Set<CarFactory> getCarFactories() {
        return carFactories;
    }

    ...

}

And the same thing for Factory class, note that there are several ways to define an embedded id or a composite id, take a look at:

Note:

In my example I haven't used transmission field in the composite id but you can use it, you can see the example below:

Embedded Primary Key

It's a good approach too and seems the most appropriate one in your case. — cнŝdk, May 08 '15 at 15:03
+1 I would recommend `@EmbeddedId` for multi-field primary keys (either composite key Entities or Join tables). We have 130 table with multi-column keys and they all use `@EmbeddedId`. We've never had any problems with any of them. — DuncanKinnear, May 11 '15 at 05:46
@chsdk my main issue is with transmission as having that as a foreign key make it unique. how to have that in the relationship table without making it unique in the parent table? — Daniel Newtown, May 27 '15 at 05:38
AFAIK, transmission is forced to be unique here because any field that's used as a foreign key is necessarly unique otherwise you can't refer it. — cнŝdk, May 27 '15 at 08:12

dedek · Answer 3 · 2015-05-05T08:52:53.217

2

Why are you not using @ManyToMany relationship?

@Entity
public class Car implements java.io.Serializable {

    @Id
    @GeneratedValue
    long id;

    @Column(name="transmission", nullable = false)
    String transmission;

    @ManyToMany
    @JoinTable(
        name="CARFACTORY",
        joinColumns={@JoinColumn(name="transmission", referencedColumnName="transmission")},
        inverseJoinColumns={@JoinColumn(name="factory_id", referencedColumnName="id")})
    Set<Factory> factories;
    ...
}

... didn't test the code, but it should work.

edited May 05 '15 at 08:52

answered May 05 '15 at 08:46

dedek

7,981
3
38
68

ManytoMany relationship is discouraged by Hibernate community. – Daniel Newtown May 08 '15 at 14:54
3

@DanielNewtown who is "Hibernate community"? Where did you read that? Could you provide any links (you made me very curious)? – V G May 11 '15 at 14:36

score 2 · Answer 4 · edited May 05 '15 at 09:06

2

There is a ManyToOne relationship in your CarFactory referencing the transmission field in Car. That means the transmission field in Car must be unique.

It seams like you are trying to add multiple items with the same transmission value to your Car table, however your design suggests you only need one entry in your Car table per transmission, and you only need to add multiple CarFactory entries per transmission.

edited May 05 '15 at 09:06

cнŝdk

31,391
7
56
78

answered May 05 '15 at 08:55

Kicsi

1,173
10
22

Can you post the part of your code where you populate the database (create the Car and CarFactory instances)? – Kicsi May 08 '15 at 15:03

score 0 · Answer 5 · answered Jun 02 '15 at 14:47

0

@JoinColumn(name="transmission",referencedColumnName = "transmission")

Change with this

@JoinColumn(name="car_id",referencedColumnName = "id")

answered Jun 02 '15 at 14:47

sgpalit

2,676
2
16
22

score 0 · Answer 6 · answered Jun 02 '15 at 22:09

Your question leaves room for interpretation: A set of factories might be necessary for one car model, which is build at different factories. Or different parts of a car instance are build at different factories. For your sample and expected data, there is no solution. If different factories can produce/have cars with the same type of transmissions, there is no way to determine, which car was produced in/is associated with the right factory. You only can say, it was one of the factories with the same transmission. However, this is not supported by any mapping. If you want to associate the right factory/factories, you need to add further information about the CarFactory to the Car table (e.g. Factory). It depends on your requirements, but I guess, the answer of chsdk comes close to them.

How to have a relationship based on a non-key field?

6 Answers6

Update