Join Tables end up with odd constraints

lally · **Joined:** Mon Mar 05, 2007 5:21 am **Posts:** 4

Hey everyone,

I've got two different Join Tables in my app, declared with Annotations. I let the annotations define the schema to be used.

Both of them end up declaring bad constraints that prevent me from using them (!). The first:

Code:

class Course {
        ....
   @org.hibernate.annotations.CollectionOfElements
   @JoinTable(
      name="CourseTags",
      joinColumns={@JoinColumn(name="COURSE_ID")},
      inverseJoinColumns={@JoinColumn(name="TAG_ID")}
   )
   Set<Tag> tags = new HashSet<Tag>();
        ....
}

TAG_ID requires uniqueness, for reasons I can't understand. I tried @JoinColumn(name="TAG_ID", unique=false), but that didn't seem to work.

Similarly,

Code:

class QuestionGroup {
        ...
   @ManyToMany
   @org.hibernate.annotations.CollectionOfElements
   @JoinTable(
      name="QuestionGroup_Question",
      joinColumns={@JoinColumn(name="QGROUPNAME_ID", referencedColumnName="id", unique=false)},
      inverseJoinColumns={@JoinColumn(name="Q_ID", referencedColumnName="id", unique=false)}
   )
   //@IndexColumn(name="ORDER_IDX", base=1)
   List<Question> questions = new ArrayList<Question>();
        ...
}

This ends up with Q_ID requiring uniqueness, which I don't want.

Anybody know how to get rid of that constraint?

Thanks in advance,

-Lally

depsypher · **Joined:** Fri Nov 30, 2007 8:40 pm **Posts:** 5

I'm running into the exact same issue... did you solve it? I've turned off the auto ddl generation and manually removed the constraint and it seems to work ok on first glance. a bit disconcerting though.

andydale · **Posted:** Mon Dec 03, 2007 8:39 am

Hi,

I do not think that both the @ManyToMany and @CollectionOfElements annotations are supported on the same collection/list/set. Also i don't understand why you are trying to use inverse join column with a collection of elements.

Cheers,

Andy

depsypher · **Joined:** Fri Nov 30, 2007 8:40 pm **Posts:** 5

My mappings are somewhat different from the original poster's, but the problem is the same. I'm mapping a collectionOfElements without the ManyToMany annotation, and using a composite key (couldn't think of another way... not happy with that part). The inverseJoinColumn is for specifying the mapping to the Ad table. Anyhow, the uniqueness constraint causing the problem in my case is on publisher_channel_opt_in.ad_id:

The entity class...

Code:

@Entity
@Table(name = "publisher_channel")
public class PublisherChannel {

    @EmbeddedId
    @AttributeOverrides({
        @AttributeOverride(name = "publisherId",
                           column = @Column(name = "publisher_id")),
        @AttributeOverride(name = "channel",
                           column = @Column(name = "channel"))
    })
    private PublisherChannelId id;

    @CollectionOfElements
    @JoinTable(
            name = "publisher_channel_opt_in",
            joinColumns = {
                    @JoinColumn(name = "channel", unique = false, nullable = false),
                    @JoinColumn(name = "publisher_id", unique = false, nullable = false)
            },
            inverseJoinColumns = @JoinColumn(name = "ad_id", unique = false, nullable = false)
    )
    @IndexColumn(name = "position", base = 1)
    private List<Ad> publisherChannelOptIn;
...

And the composite key class...

Code:

public class PublisherChannelId implements Serializable {

    private static final long serialVersionUID = 1L;

    private String publisherId;
    private String channel;

    public PublisherChannelId() {
    }

    public PublisherChannelId(String publisherId, String channel) {
        this.publisherId = publisherId;
        this.channel = channel;
    }
...

The tables this creates look like this (the UNI constraint on ad_id is the problem):

Code:

mysql> describe publisher_channel_opt_in;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| channel      | varchar(255) | NO   | PRI |         |       | 
| publisher_id | varchar(255) | NO   | PRI |         |       | 
| ad_id        | int(11)      | NO   | UNI |         |       | 
| position     | int(11)      | NO   | PRI |         |       | 
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe publisher_channel;       
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| channel      | varchar(255) | NO   | PRI |         |       | 
| publisher_id | varchar(255) | NO   | PRI |         |       | 
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

btw, the thing I'm trying to accomplish is making this bit of currently hard-coded data persistent. I'm still learning hibernate, so if I'm doing things the hard way out of ignorance (likely) I'd appreciate a helping hand:

Code:

    final Map<String, List<ChannelAds>> targetedChannels;

    public static class ChannelAds {
        private String channel;
        private List<Integer> ads;

        public ChannelAds(String channel, Integer... ads) {
            this.channel = channel;
            this.ads = Arrays.asList(ads);
        }
    }

andydale · **Posted:** Tue Dec 04, 2007 3:44 am

Hi,

Is the Ad class an entity ? if so you probably want to drop the @CollectionOfElements and make a real relationship between the entities with an anonnotation such as @ManyToOne/@OneToMany

Cheers,

Andy

depsypher · **Joined:** Fri Nov 30, 2007 8:40 pm **Posts:** 5

I've replaced the @CollectionOfElements with a @OneToMany association but am getting the same behavior. The tables it creates are the same, and the unique constraint is still there, and the behavior is still solved by manually removing the constraint. Could this be a bug in hbm2ddl?

Code:

    @OneToMany
    @JoinTable(
            name = "publisher_channel_opt_in",
            joinColumns = {
                    @JoinColumn(name = "channel"),
                    @JoinColumn(name = "publisher_id")
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "ad_id", unique = false)
            }
    )
    @IndexColumn(name = "position")
    private List<Ad> publisherChannelOptIn = new ArrayList<Ad>();

andydale · **Posted:** Wed Dec 05, 2007 10:14 am

Hi

From the the hibernate documentation http://www.hibernate.org/hib_docs/annotations/reference/en/html_single/

Quote:

2.2.5.3.2.4. Defaults

Without describing any physical mapping, a unidirectional one to many with join table is used. The table name is the concatenation of the owner table name, _, and the other side table name. The foreign key name(s) referencing the owner table is the concatenation of the owner table, _, and the owner primary key column(s) name. The foreign key name(s) referencing the other side is the concatenation of the owner property name, _, and the other side primary key column(s) name. A unique constraint is added to the foreign key referencing the other side table to reflect the one to many.

So it seems the unique constraint is a default, i have experimented with just a simple @OneToMany and it creates it for me too. What cuases the issue is the inverseJoinColumns setting.

I did a little experiment which produced the correct join table, do this

In Ad do the following:

Code:

   @ManyToOne
   @JoinTable(
          name = "publisher_channel_opt_in",
          joinColumns = {
                  @JoinColumn(name = "ad_id", unique = false, nullable = false) },
                  inverseJoinColumns = {
                   @JoinColumn(name = "channel", unique = false, nullable = false),
                   @JoinColumn(name = "publisher_id", unique = false, nullable = false)
          }
   )
   private PublisherChannel pub;

In PublisherChannel:

Code:

@OneToMany(mappedBy = "pub")
private Collection<Ad> publisherChannelOptIn = new ArrayList<Ad>();

The above should work with a list and index column

This generates the below table in postgres

Code:

CREATE TABLE publisher_channel_opt_in
(
  ad_id int8 NOT NULL,
  channel varchar(255) NOT NULL,
  publisher_id varchar(255) NOT NULL,
  CONSTRAINT publisher_channel_opt_in_pkey PRIMARY KEY (ad_id),
  CONSTRAINT fka902dd90733335f6 FOREIGN KEY (ad_id)
      REFERENCES ad(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fka902dd90ad0681ac FOREIGN KEY (channel, publisher_id)
      REFERENCES publisher_channel (publisherid, channel) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 

Cheers,

Andy

depsypher · **Joined:** Fri Nov 30, 2007 8:40 pm **Posts:** 5

Thanks, I really appreciate your help... I had tried something similar, but the problem is that this changes the primary key in publisher_channel_opt_in to be ad_id, which isn't what I need. I need a channel and publisher_id composite primary key.

If in your example, you reverse the joinColumns and the inverseJoinColumns in the @ManyToOne join table you could get a little farther, but everything I've tried insists on making ad_id unique in the join table.

It seems to me that if you specify unique = false on the inverseJoinColumn, that's what you should get... feels like a bug to me, but being a newbie it's not a conclusion I want to jump to.

andydale · **Posted:** Thu Dec 06, 2007 3:23 am

Hi,

I don't know if this is possible with a @OneToMany / @ManyToOne relationship. If you switch it to @ManyToMany you get the following join table

Code:

=== NOTE: pubs is the collection in Ad ===

CREATE TABLE publisher_channel_ad
(
  pubs_publisherid varchar(255) NOT NULL,
  pubs_channel varchar(255) NOT NULL,
  publisherchanneloptin_id int8 NOT NULL,
  CONSTRAINT publisher_channel_tag_pkey PRIMARY KEY (pubs_publisherid, pubs_channel, publisherchanneloptin_id),
  CONSTRAINT fk321ea07b3a49f23f FOREIGN KEY (publisherchanneloptin_id)
      REFERENCES Ad (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk321ea07b449ddbb3 FOREIGN KEY (pubs_publisherid, pubs_channel)
      REFERENCES publisher_channel (publisherid, channel) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 

Which might be what you would want at DB level ? but i don't know if changing your domain model is a good idea.

Cheers,

Andy

depsypher · **Joined:** Fri Nov 30, 2007 8:40 pm **Posts:** 5

Yup, this does seem to do the trick. I'm going ahead with this approach. Thanks a lot for your help!