@@ -, +, @@ --- installer/data/mysql/atomicupdate/bug_22887.perl | 16 ++++++++++++++-- installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 15 insertions(+), 3 deletions(-) --- a/installer/data/mysql/atomicupdate/bug_22887.perl +++ a/installer/data/mysql/atomicupdate/bug_22887.perl @@ -1,8 +1,20 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { - $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `category_code` UNIQUE (category,authorised_value)} ); + unless ( index_exists('authorised_values', 'av_uniq') ) { + my $duplicates = $dbh->selectall_arrayref(q| + SELECT category, authorised_value, COUNT(concat(category, ':', authorised_value)) AS c + FROM authorised_values + GROUP BY category, authorised_value + HAVING c > 1 + |, { Slice => {} }); + if ( @$duplicates ) { + warn "WARNING - Cannot create unique constraint on authorised_value(category, authorised_value)\n"; + warn "The following entries are duplicated: " . join (', ', map { sprintf "%s:%s (%s)", $_->{category}, $_->{authorised_value}, $_->{c} } @$duplicates); + } else { + $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `av_uniq` UNIQUE (category, authorised_value)} ); + } + } - # Always end with this (adjust the bug info) SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 22887 - Add unique constraint to authorised_values)\n"; } --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -119,7 +119,7 @@ CREATE TABLE `authorised_values` ( -- stores values for authorized values catego KEY `name` (`category`), KEY `lib` (`lib` (191)), KEY `auth_value_idx` (`authorised_value`), - CONSTRAINT `category_code` UNIQUE (`category`,`authorised_value`), + CONSTRAINT `av_uniq` UNIQUE (`category`,`authorised_value`), CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --