Close

JPA - Applying Unique Constraints by using @Table#uniqueConstrains

[Updated: Oct 22, 2018, Created: Oct 22, 2018]

The attribute 'uniqueConstraints' of @Table annotation can be used to apply unique constraints to the specified fields.

@Table annotation:

package javax.persistence;
....
public @interface Table {
....
    /**
     * (Optional) Unique constraints that are to be placed on 
     * the table. These are only used if table generation is in 
     * effect. These constraints apply in addition to any constraints 
     * specified by the <code>Column</code> and <code>JoinColumn</code> 
     * annotations and constraints entailed by primary key mappings.
     * <p> Defaults to no additional constraints.
     */
    UniqueConstraint[] uniqueConstraints() default {};
....
}

@UniqueConstraint annotation:

package javax.persistence;
....
public @interface UniqueConstraint {
    /** (Optional) Constraint name.  A provider-chosen name will be chosen
     * if a name is not specified.
     *
     * @since Java Persistence 2.0
     */
    String name() default "";

    /** (Required) An array of the column names that make up the constraint. */
    String[] columnNames();
}

Example

Entity

@Entity
@Table(uniqueConstraints = {
      @UniqueConstraint(columnNames = "name", name = "uniqueNameConstraint")}
)
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String dept;
    .............
}

Main class

Checking generated constraints and persisting/loading data:

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          //constraint assigned to Employee table:
          runNativeQuery("SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME='EMPLOYEE'");
          //column names of CONSTRAINTS table:
          runNativeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='CONSTRAINTS'");
          persistEmployees();
          findAllEmployees();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findAllEmployees() {
      System.out.println("-- Employees with phone number NOT 222-222-222 --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      TypedQuery<Employee> query = entityManager
              .createQuery("Select e from Employee e", Employee.class);
      List<Employee> list = query.getResultList();
      list.forEach(System.out::println);
      entityManager.close();
  }

  private static void runNativeQuery(String s) {
      System.out.printf("-- %s --%n", s);
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      Query query = entityManager.createNativeQuery(s);
      List list = query.getResultList();
      for (Object o : list) {
          if (o instanceof Object[]) {
              System.out.println(Arrays.toString((Object[]) o));
          } else {
              System.out.println(o);
          }
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.of("Diana", "IT");
      Employee employee2 = Employee.of("Mike", "Admin");
      Employee employee3 = Employee.of("Tim", "Sales");

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.getTransaction().commit();
      em.close();
  }
}
-- SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME='EMPLOYEE' --
[TEST, PUBLIC, UNIQUENAMECONSTRAINT, UNIQUE, TEST, PUBLIC, EMPLOYEE, UNIQUENAMECONSTRAINT_INDEX_7, null, NAME, , ALTER TABLE PUBLIC.EMPLOYEE ADD CONSTRAINT PUBLIC.UNIQUENAMECONSTRAINT UNIQUE(NAME) INDEX PUBLIC.UNIQUENAMECONSTRAINT_INDEX_7, 9]
[TEST, PUBLIC, CONSTRAINT_7, PRIMARY_KEY, TEST, PUBLIC, EMPLOYEE, PRIMARY_KEY_7, null, ID, , ALTER TABLE PUBLIC.EMPLOYEE ADD CONSTRAINT PUBLIC.CONSTRAINT_7 PRIMARY KEY(ID) INDEX PUBLIC.PRIMARY_KEY_7, 7]
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='CONSTRAINTS' --
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
CONSTRAINT_TYPE
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
UNIQUE_INDEX_NAME
CHECK_EXPRESSION
COLUMN_LIST
REMARKS
SQL
ID
-- Employees with phone number NOT 222-222-222 --
Employee{id=1, name='Diana', dept='IT'}
Employee{id=2, name='Mike', dept='Admin'}
Employee{id=3, name='Tim', dept='Sales'}

Also see Queries involving INFORMATION_SCHEMA.

Violating unique constraint

If we try to save the same name for multiple Employees then ConstraintViolationException will be thrown:

public class ExampleMain2 {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.of("Diana", "IT");
      Employee employee2 = Employee.of("Diana", "Admin");

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.getTransaction().commit();
      em.close();
  }
}
......
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3171)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3686)
	at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:90)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
	at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283)
	at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
	... 2 more
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "UNIQUENAMECONSTRAINT_INDEX_7 ON PUBLIC.EMPLOYEE(NAME) VALUES ('Diana', 1)"; SQL statement:
insert into Employee (dept, name, id) values (?, ?, ?) [23505-197]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:101)
	at org.h2.mvstore.db.MVSecondaryIndex.requireUnique(MVSecondaryIndex.java:236)
	at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:202)
	at org.h2.mvstore.db.MVTable.addRow(MVTable.java:732)
	at org.h2.command.dml.Insert.insertRows(Insert.java:182)
	at org.h2.command.dml.Insert.update(Insert.java:134)
	at org.h2.command.CommandContainer.update(CommandContainer.java:102)
	at org.h2.command.Command.executeUpdate(Command.java:261)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
	... 18 more

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • hibernate-jpamodelgen 5.3.6.Final: Annotation Processor to generate JPA 2 static metamodel classes.
  • JDK 1.8
  • Maven 3.5.4

JPA - Applying unique constraints using @Table annotation Select All Download
  • jpa-table-annotation-unique-constraints
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • Employee.java
          • resources
            • META-INF

    See Also