Add column to Join table?

Question

I am having some trouble adding a column to an existing join table. Below is how I want my join table to look like, but I am missing the tenant_id column in the join table in my actual implementation.

Below is my code for my JPA implementation:

@Entity
@Table(name = "label")
public class Label {
    @Column(name = "tenant_id")
    private String tenant_id;

    @Column(name = "label_id")
    private String label_id;

    @JsonIgnore
    @ManyToMany(targetEntity = Report.class, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinTable(name = "tagging", joinColumns = @JoinColumn(name = "label_id"), inverseJoinColumns = @JoinColumn(name = "report_id"))
    private Set<Report> reports;
}

@Entity
@Table(name = "report")
public class Report {
    @Column(name = "tenant_id")
    private String tenant_id;

    @Column(name = "report_id")
    private String report_id;

    @column(name = "created_by")
    private String created_by;

    @JsonIgnore
    @ManyToMany(targetEntity = Label.class, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinTable(name = "tagging", joinColumns = @JoinColumn(name = "report_id"), inverseJoinColumns = @JoinColumn(name = "label_id"))
    private Set<Label> labels;

}

And the code basically puts the id of the Report class and Label class into the tagging table in place of the report_id and label_id columns respectively. The issue I'm having is that I want to add the tenant_id column to every existing entry in my tagging table. I tried

ALTER TABLE tagging ADD COLUMN tenant_id varchar(255) NOT NULL;

but because I have pre-existing data, I get an error saying that the tenant_id must have a default value. I want to somehow update every tagging table with the following:

select tenant_id from report where report.id = tagging.report_id, and perform this update the moment the values in my database are populated/created. Is there any way to do this? Any help would be appreciated. Thanks!

I am confused slightly by your schema and your question however note that if you wish to record additional info about the relationship then you need to replace the '@ManyToMany' by creating an additional entity pointing to "tagging" and give both Report and Label a '@OneToMany' relationship to this new Entity. See here: http://stackoverflow.com/questions/5127129/mapping-many-to-many-association-table-with-extra-columns — Alan Hay, Feb 08 '16 at 11:22
@AlanHay Thanks for letting me know. Are you saying that with any new rows I want to insert into tagging, I have to create an `Entity` called `tagging` and make a `@OneToMany` relationship with `report_id`, `label_id`, and `tenant_id`? — user1871869, Feb 08 '16 at 21:08

score 1 · Answer 1 · answered Feb 08 '16 at 06:12

I'm not sure, did I understand you problem properly. But if you need to add tenant_id and fill it you can do next.

First, add new column with default null:

ALTER TABLE tagging ADD COLUMN tenant_id varchar(255) DEFAULT NULL;

Second, update all tenant_id:

UPDATE tagging t SET t.tenant_id = (SELECT r.tenant_id from report r WHERE r.id = t.report_id);

Third, modify tagging table constraint:

ALTER TABLE tagging MODIFY COLUMN tenant_id varchar(255) NOT NULL;

Note, that you must be sure, that your report table not containg NULL value in tenant_id column.

score 0 · Answer 2 · answered Feb 08 '16 at 06:07

0

Just alter tagging (class)table as you mentioned above but add default value, that exists in your label table(class) and then update tagging table(class) as you want to.

answered Feb 08 '16 at 06:07

jvb

61
6

Add column to Join table?

2 Answers2