Creating a Composite Unique Constraints on Multiple Columns

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 the where clause
  • filtering on index_id and index_date in the where clause
  • filtering on index_id in the where clause and the ordering by index_date

The second index would not be used in these circumstance. It would be used for:

  • filtering on index_date in the where 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:

  1. 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.
  2. 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 run liquibase updateSQL.


Related Topics



Leave a reply



Submit