Multi column unique index created two indexes - for list of column and for single column which breaks database schema #617
Description
I think this is a Blocker issue. If one defines unique constraint for more than one column, two indexes will be created in database. Having a domain
class DomainOne {
String controller
String action
static constraints = {
action unique: 'controller'
}
}
Creates DDL (using gradle schemaExport):
drop table domain_one if exists;
create table domain_one (
id bigint generated by default as identity,
version bigint not null,
action varchar(255) not null,
controller varchar(255) not null,
primary key (id)
);
alter table domain_one
add constraint unique_action unique (controller, action);
alter table domain_one
add constraint UK_68gabj3tgv6og4w1g9dh8evk unique (action);
As a result, if we want to create domains, which should not break unique constraint:
new DomainOne(controller: 'project', action: 'update').save()
new DomainOne(controller: 'project', action: 'delete').save()
new DomainOne(controller: 'projectTask', action: 'update').save() //this will fail!
Last save causes exception
Unique index or primary key violation: "UK_68GABJ3TGV6OG4W1G9DH8EVK_INDEX_D ON PUBLIC.DOMAIN_ONE(ACTION) VALUES ('update', 1)"; SQL statement:
insert into domain_one (id, version, action, controller) values (null, ?, ?, ?) [23505-190]
Project with code to reproduce is here: https://github.com/droggo/MultiColumnUniqueIndexError
Testing on:
Grails 3.1.0.RC1
Hibernate 4
H2 DB