Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating a composite Unique constraints on multiple columns

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?

like image 947
ripper234 Avatar asked Nov 23 '11 16:11

ripper234


People also ask

Can we apply unique constraint on multiple columns?

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.

How do I create a unique constraint on multiple columns in SQL Server?

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.

How do I create a unique constraint on multiple columns in MySQL?

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.

Can composite unique constraints be created?

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.


2 Answers

Use @UniqueConstraint:

@Table(     uniqueConstraints=         @UniqueConstraint(columnNames={"author_id", "number"}) ) @Entity class Book extends Model {    @ManyToOne    @JoinColumn(name = "author_id")    User author;    int number;  }  
like image 132
axtavt Avatar answered Sep 18 '22 14:09

axtavt


When table is created before, it is necessary to remove it. Unique key is not added to existing table.

like image 34
Tomasz Janisiewicz Avatar answered Sep 20 '22 14:09

Tomasz Janisiewicz