title: MySQLIntegrityConstraintViolationException when updating list of entities mapped as @ManyToMany, @JoinTable and @OrderColumn (with unique constraint)
body: <p>I have a many-to-many relationship between <code>Prequalification</code> and <code>Company</code> entities (<code>Partnership</code>). The DDL for the three tables is:</p>
  
  <pre><code>CREATE TABLE Prequalifications
  (
    id              INTEGER      NOT NULL IDENTITY,
    user_id         INTEGER      NOT NULL,
    name            VARCHAR(100) NOT NULL,
  
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES Users (id)
  );
  
  CREATE TABLE Partnerships
  (
    prequalification_id INTEGER            NOT NULL,
    company_id          INTEGER            NOT NULL,
    ordinal_nbr         SMALLINT DEFAULT 0 NOT NULL,
  
    PRIMARY KEY (prequalification_id, company_id),
    FOREIGN KEY (prequalification_id) REFERENCES Prequalifications (id),
    FOREIGN KEY (company_id) REFERENCES Companies (id),
    UNIQUE (prequalification_id, ordinal_nbr)
  );
  
  CREATE TABLE Companies
  (
    id       INTEGER     NOT NULL,
    dnd_type VARCHAR(10) NOT NULL,
  
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES Organizations (id) -- just an inherited ID, never mind!
  );
  </code></pre>
  
  <p>Please note the <strong>unique constraint</strong> on <code>Partnerships</code>'s PQ ID and ordinal number: there can be only one position of a company per PQ.</p>
  
  <p>This got mapped in <code>Prequalification</code> as <code>@ManyToMany</code> + <code>@JoinTable</code> including an <code>@OrderColumn</code> for the order of the companies per PQ (<code>Partnerships</code>'s order column is ordinal_nbr):</p>
  
  <pre><code>@Entity
  @Table(name = "Prequalifications")
  public class Prequalification implements Serializable
  {
      ...
  
      @ManyToMany
      @JoinTable(name = "Partnerships", joinColumns = @JoinColumn(name = "prequalification_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "company_id", referencedColumnName = "id"))
      @OrderColumn(name = "ordinal_nbr", nullable = false)
      private List&lt;Company&gt; companies;
  
      ...
  }
  </code></pre>
  
  <p>Here's the update method which is called from the GUI. Note, that the list on the GUI only includes external companies and that the internal company is (supposed to be) at index zero always:</p>
  
  <pre><code>@ManagedBean
  @ViewScoped
  public class PqHome implements DropListener, Serializable
  {
      ...
  
      private Prequalification pq;
      private Integer userId;
      private List&lt;Company&gt; participatingCompanies; // xetters omitted!
  
      ...
  
      public void update()
      {
          // assign new owner
          pq.setUser(userService.findSingleUser(userId));
  
          Company internal = companyManager.getInternalCompany();
  
          // find internal company
          int index = participatingCompanies.indexOf(internal);
  
          // if internal company missing or at non-zero index: we need to repair this
          if ( index != 0 )
          {
              // if internal exists at some wrong place, remove it
              if ( index &gt; 0 )
              {
                  participatingCompanies.remove(index);
              }
  
              // (re-)add at index zero
              participatingCompanies.add(0, internal);
          }       
  
          pq.setCompanies(participatingCompanies);
  
          // update and get *new* merged instance
          pq = pqService.update(pq);
  
          participatingCompanies = null;
          init(); // some not so important (re-)initialization...
      }
  
      ...
  }
  </code></pre>
  
  <p>In the client JSF page the field <code>participatingCompanies</code> is used like:</p>
  
  <pre><code>&lt;rich:pickList value="#{pqHome.participatingCompanies}"
                 var="company"
                 converter="#{companyConverter}"
                 orderable="true"
                 sourceCaption="Available companies"
                 targetCaption="Selected companies"&gt;
    &lt;f:selectItems value="#{companyManager.externalCompanies}" /&gt;
    &lt;rich:column&gt;#{company.name}&lt;/rich:column&gt;
  &lt;/rich:pickList&gt;
  </code></pre>
  
  <p>Don't be intimidated RichFaces component. The list referencing <code>#{pqHome.participatingCompanies}</code> just contains optional (external) companies.</p>
  
  <p><img src="https://i.stack.imgur.com/BC1FD.png" alt="screenshot"></p>
  
  <p>When I hit the update button (not shown), the <code>update</code> method on the <code>PqHome</code> bean is called. When executing the code on <strong>GlassFish 3.1.2 using EclipseLink 2.3.2</strong> the following exception is thrown:</p>
  
  <pre><code>Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-2' for key 'partnerships_multi_uq'
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.Util.getInstance(Util.java:386)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
      at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:875)
      at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:831)
      ... 128 more
  </code></pre>
  
  <p>This exception is duplicated several times in the log. Other PQ's have the same problem, just involve other unique column combinations, e.g. '1-1'. The server log can be found here: <a href="http://www.eclipse.org/forums/index.php/t/310923/" rel="nofollow noreferrer">http://www.eclipse.org/forums/index.php/t/310923/</a></p>
  
  <p><strong>What's wrong with the update code?</strong> It works without problems on Hibernate.</p>