This is my model:
class User {...} class Book { User author; int number; }
Every book number starts at 1 per author and increments upwards. So we'll have Books 1,2,3 by John Grisham, Book 1..5 by George Martin, etc...
Is there a unique constraint I can place on Book
, that would guarantee we don't have two books with the same number by the same author? Similar to @Column(unique = true)
, but the constraint only applies on the composite of Author X number
?
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 KeysTo define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax. To satisfy a constraint that designates a composite unique key, no two rows in the table can have the same combination of values in the key columns.
Use @UniqueConstraint
:
@Table( uniqueConstraints= @UniqueConstraint(columnNames={"author_id", "number"}) ) @Entity class Book extends Model { @ManyToOne @JoinColumn(name = "author_id") User author; int number; }
When table is created before, it is necessary to remove it. Unique key is not added to existing table.
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