title: How do I delete a row in a join table automatically, to avoid a ConstraintViolationException?
body: <p>This seems like it should be a pretty simple question, or at least have a simple answer. But -  I'm really not a database guy, and I'm still pretty far down on the Hibernate learning curve. That said, here's the setup:</p>
  
  <p>Consider a unidirectional many-to-many relationship between two entities, from <code>Foo</code> to <code>Bar</code>:</p>
  
  <p><sup>(pardon any typos below, the following is obviously a simplification of the actual code)</sup></p>
  
  <h3>FooDTO.java:</h3>
  
  <pre><code>@Entity
  @Table(name = "MyDB.dbo.Foo")
  class FooDTO implements Serializable
  {
      private int id;
      private String name;
      private Set&lt;BarDTO&gt; bars = new HashSet&lt;BarDTO&gt;();
  
      ...
  
      @Fetch(FetchMode.JOIN)
      @ManyToMany(fetch = FetchType.EAGER)
      @JoinTable(name = "MyDB.dbo.FooBar",
                 joinColumns = { @JoinColumn(name = "fooId") },
                 inverseJoinColumns = { @JoinColumn(name = "barId") })
      public Set&lt;BarDTO&gt; getBars()
      {
          return bars;
      }
      public void setBars(Set&lt;Bar&gt; bars)
      {
          this.bars = bars;
      }
  }
  </code></pre>
  
  <h3>BarDTO.java:</h3>
  
  <pre><code>@Entity
  @Table(name = "MyDB.dbo.Bar")
  class BarDTO implements Serializable
  {
      private int id;
      private String name;
  
      ...
  }
  </code></pre>
  
  <p>On the TSQL side, the join table is set up like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[FooBar](
      [id] [int] NOT NULL IDENTITY PRIMARY KEY,
      [fooId] [int] NOT NULL,
      [barId] [int] NOT NULL,
      CONSTRAINT fk_FooBar_FooId FOREIGN KEY (fooId) REFERENCES [dbo].[Foo](id),
      CONSTRAINT fk_FooBar_BarId FOREIGN KEY (barId) REFERENCES [dbo].[Bar](id),
  ) ON [PRIMARY]
  END
  </code></pre>
  
  <p>If I try to outright delete a <code>BarDTO</code>, I get a <code>ConstraintViolationException</code> because I haven't first deleted the row in the join table (duh).</p>
  
  <h3>Questions:</h3>
  
  <ul>
  <li>Can I get Hibernate to drop the rows in the join table automagically, when I delete a <code>Bar</code>? How?</li>
  <li>If not, how do I select all the <code>Foo</code>s that have a particular <code>Bar</code>, so I can remove that <code>Bar</code> from each relevant <code>Foo</code>'s set of <code>Bar</code>s?</li>
  </ul>
  
  <p>With regard to the latter question, I <em>think</em> this could be done with either a <code>NamedQuery</code> or using the Criteria API, but I don't know specifically how to write such a query or which constraints to apply. I think it would a named query would look something like:</p>
  
  <pre><code>SELECT f FROM FooDTO f INNER JOIN ??? WHERE f.id = ???
  </code></pre>
  
  <p>but I'm not sure where the <code>barId</code> parameter would go, or how to join on the <code>FooBar</code> table since I don't declare it as an entity. (Side note, I also recall previous issues with trying to join in a named query - is joining in a named query impossible?)</p>