How to use uniqueConstraint on referenced table column with JPA

Question

How can I include a referenced table column in a unique constraint? I want to create a unique constraint for table Survey for columns toEmail, receipt.receiptSeries and receipt.receiptNum.

See entities below please:

Receipt

@Entity
@Table(name = "RECEIPT")
public class Receipt {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @Column(name = "RECEIPT_SERIES")
  private String receiptSeries;

  @Column(name = "RECEIPT_NUM")
  private String receiptNum;
}

Survey

@Entity
@Table(name = "SURVEY"}
public class Survey {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @ManyToOne(optional = false)
  @JoinColumn(name = "RECEIPT_ID")
  private Receipt receipt;

  @Column(name = "TO_EMAIL")
  private String toEmail;
}

I've tried using the approach below with no success:

@Table(name = "SURVEY", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"TO_EMAIL", "RECEIPT.RECEIPT_SERIES", "RECEIPT.RECEIPT_NUM"})})

Using a composite primary key with receiptSeries and receiptNum for Receipt is not an option.

you can try with `@Column(name = "TO_EMAIL",unique=true)` suppose this [post](https://stackoverflow.com/questions/3126769/uniqueconstraint-annotation-in-java) may a be helpful to you — Rajith Pemabandu, Jul 21 '17 at 00:34
@RajithPemabandu Yes, but this would make the `TO_EMAIL` column unique on `SURVEY` table. In my question there could be more than one `SURVEY` with same email, but not for the same `RECEIPT_SERIES` / `RECEIPT_NUM` combination — Bruno Leite, Jul 21 '17 at 01:21

score 0 · Answer 1 · answered Jun 02 '20 at 11:38

Try something like this

@Table(name="notification_status", uniqueConstraints= 
       arrayOf(UniqueConstraint(columnNames= arrayOf("external_user_id",
                                                     "external_organization_id",  
                                                     "notification_id"))))

How to use uniqueConstraint on referenced table column with JPA

1 Answers1