How to add uniqueConstraint on two columns together

[cash1981]

I have two columns that I want together to be unique validated.

I have column username and a ManyToOne organization that together must validate unique.

ie, if the table has these values:


select * from table
username = admin
organization = Foo

and we insert:
username = admin
organization = Bar

Result should be ok. and this should fail:
insert:
username = admin
organization = Foo

But the following annotation on my Entity doesnt work
@UniqueConstraint(columnNames = { "username", "organization_id" })
@Entity
public class ....

@ManyToOne
private Organization organization;

@UserPrincipal
@Column
private String username;

I have solved it by creating a trigger @PreUpdate and @PrePersist that manually checks it, but it would be nice to have the database constraint.
Any help?
PS: Sorry about formatting, but I couldnt get code block to work

[gonorrhea]

If you know how to write the DDL in T-SQL or PL/SQL, etc. you should do that to mod the table definition and then reverse engineer the table.

Here is an example with MSSQL:

ALTER TABLE [dbo].[ApplicationSettingValue] ADD  CONSTRAINT [UK_APPLICATIONSETTINGVALUE_SETTINGID_SITEID] UNIQUE NONCLUSTERED 
(
     [SettingID] ASC,
     [SiteID] ASC
)

Entity
@Table(name = "ApplicationSettingValue", uniqueConstraints = @UniqueConstraint(columnNames = {
          "SettingID", "SiteID"}))
public class ApplicationSettingValue implements java.io.Serializable {
...
}

[cash1981]

Thanks, I will try your solution although what I still wonder about is what should be the columnName for the ManyToOne.

The table says organization_id. My guess thats the correct one to use.