Creating a composite Unique constraints on multiple columns
Use @UniqueConstraint
:
@Table(
uniqueConstraints=
@UniqueConstraint(columnNames={"author_id", "number"})
)
@Entity
class Book extends Model {
@ManyToOne
@JoinColumn(name = "author_id")
User author;
int number;
}
How do I create unique constraint for multiple columns?
How do I specify unique constraint for multiple columns in MySQL?
To add a unique constraint, you need to use two components:
ALTER TABLE
- to change the table schema and,
ADD UNIQUE
- to add the unique constraint.
You then can define your new unique key with the format 'name'('column1', 'column2'...)
So for your particular issue, you could use this command:
ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
unique constraints for multiple columns in mysql
You need to add a Composite UNIQUE constraint on COMPANY_ID
and BARCODE
, to your table. This basically means that it will not allow rows having duplicate combination of values, for the said two fields. It can still allow duplicate values individually for either of them.
Eg: (1, 'abc')
combination will not be able to exist in more than one row. However, (1, 'abc')
, (1, 'def')
can exist. So it allows duplicate values individually (Company_id
in this example)
Do the following (change table and column name(s) accordingly):
ALTER TABLE your_table_name
ADD CONSTRAINT unique_barcode_company UNIQUE (COMPANY_ID, BARCODE)
Does columns order in multiple columns unique constraint make any difference? Is it justifiable to have duplicate indexes?
Your question is:
Would it be justified, in certain situations, to create two indexes?
One, multiple column index, on (index_id, index_date) and second,
single column index, on (index_date)?
The answer is "yes". The first index will be used to satisfy queries with conditions like:
- filtering on
index_id
in thewhere
clause - filtering on
index_id
andindex_date
in thewhere
clause - filtering on
index_id
in thewhere
clause and the ordering byindex_date
The second index would not be used in these circumstance. It would be used for:
- filtering on
index_date
in thewhere
clause
And the first index would not be used in this case.
The ordering of columns in indexes is important. They are used from the left to the right. So, these two indexes are useful. However, a third index on index_id
alone would not be useful, because the first index already takes care of the same situations where that index would be used.
Unique constraint on multiple columns
By using the constraint definition on table creation, you can specify one or multiple constraints that span multiple columns. The syntax, simplified from technet's documentation, is in the form of:
CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ]
(
column [ ASC | DESC ] [ ,...n ]
)
Therefore, the resuting table definition would be:
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
),
CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED
(
[fcode], [scode], [dcode]
)
) ON [PRIMARY]
How To Use A Unique Constraint On Multiple Columns Instead Of Creating A Composite Key
You could do that if you're allowed to add a new column that'll be the real primary key, e.g. an autoincrement or a sequence-based column. Then adding the unique constraint would be simple:
class SecUserSecRole {
SecUser secUser
SecRole secRole
static constraints = {
secUser(unique:'secRole')
}
}
But I doubt that's an option :)
Note that Grails 2.0 has support for Hibernate Bags, which addresses the collection performance problems that this SecUserSecRole approach works around.
Adding composite unique constraint in Liquibase
I am pretty certain that:
- You can't do it inside the createTable tag itself, but you can do it within the same changeset as when the table is created.
- It does create a composite unique constraint on the two columns. One way you can check is to run liquibase with the command to generate the SQL for update rather than running the update command and check what it does for your database. On the command line, rather than running
liquibase update
you would runliquibase updateSQL
.
Related Topics
How to Check Type of Variable in Java
How to Print Out All the Elements of a List in Java
What Happens When Base and Derived Classes Each Have Variables With the Same Name
Why Is My Spring @Autowired Field Null
Java: How to Check If Object Is Null
Error: Incompatible Types: Int[][] Cannot Be Converted to Int
Spring Rest Post Json Requestbody Content Type Not Supported
Flush/Clear System.In (Stdin) Before Reading
Java 8 Convert Utc Time to Edt/Est So That Date Remains the Same
Selecting from Div Class Dropdown - Selenium
Calling Method That Exists in Child Classes But Not in Parent Class
How to Pass Topics Dynamically to a Kafka Listener
Return Json Object from a Spring-Boot Rest Controller
Spring Security - 405 Request Method 'Post' Not Supported
How to Handle Incorrect User Input into a Scanner
How to Exclude Property from Lombok Builder
How to Fix: Error Creating Bean With Name:Unsatisfied Dependency Expressed Through Field