I am using SEAM 2/Hibernate along with PostgreSQL 9 database. I have the following table
Active Band =========== active_band_id serial active_band_user text active_band_date timestamp active_band_process integer
I would like to add a constraint that ensures each new entry has a unique combination of active_band_user and active_band_date.
There could potentially be many attempted inserts per second so I need this to be as efficient as possible, is there a SEAM / hibernate annotation I can use in the entity mapping?
Thanks in advance
You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns. Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation.
SQL UNIQUE constraint for 2 columns example Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want. To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
Defining Composite Unique Keys Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns.
There is no Hibernate annotation that checks uniqueness before insert/update. But there is annotation which will generate such a constraint to database if automatic database creation is used:
@Table( name="ACTIVE_BAND", uniqueConstraints= @UniqueConstraint(columnNames={"active_band_user", "active_band_date"}) )
In a more modern syntax, it would be :
@Table( name="ACTIVE_BAND", uniqueConstraints = [UniqueConstraint( columnNames = ["active_band_user", "active_band_date"] )] )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With