/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::Diff::Index

NAME

DBIO::MySQL::Diff::Index - Diff operations for MySQL/MariaDB indexes

VERSION

version 0.900004

DESCRIPTION

Represents an index-level diff operation: CREATE INDEX or DROP INDEX. Every index is diffed here except the PRIMARY KEY (introspect origin pk), which DBIO::MySQL::Diff::Table writes into CREATE TABLE.

That includes the unique indexes (origin u). The server keeps a UNIQUE constraint as a unique index and nothing else, the two cannot be told apart, and DBIO::MySQL::Introspect::Indexes gives u to every unique index. So a unique key that is added to, changed in or removed from a schema is a CREATE UNIQUE INDEX or a DROP INDEX like that of any other index.

An index has changed when its uniqueness differs, when the set of its columns differs, or when both sides name an index type and the two differ (see "changed_index_fields" in DBIO::Diff::Compare; the same columns in another order are not a change). A changed index is dropped and created again.

MariaDB and FULLTEXT. A FULLTEXT index that is dropped and created again does not find the rows written while the old index existed, until the table is rebuilt (measured on MariaDB 11.8, reproducible with plain SQL; MySQL 8.0 finds them). The diff creates the index correctly and does not rebuild the table; if old rows must be found after such an upgrade on MariaDB, run ALTER TABLE ... FORCE on the table afterwards.

The CREATE TABLE of DBIO::MySQL::Diff::Table carries no index but the primary key. The indexes of a table the diff creates, its unique keys among them, follow as statements of their own, after the tables and the column changes of the same diff.

A DROP INDEX stands before the column changes of the same diff (DBIO::MySQL::Diff puts it there), so a column that leaves the schema together with an index over it is dropped after that index. The other way round the server takes an index along with the last column it was over, and a DROP INDEX after that does not find it; MariaDB refuses to drop one column of a unique key over several; and MySQL keeps such a key over the columns that are left, where the rows may no longer differ. A changed index is dropped before the column changes and created after them.

That order would leave the foreign keys without an index, so the diff looks after them. The foreign keys that leave the schema, or change, are dropped before everything else, and those that arrive are added last (DBIO::MySQL::Diff::ForeignKey), so the index a foreign key had to have goes after the constraint. A foreign key in the CREATE TABLE of a table the diff creates cannot reference a key that is created by the same diff, because the key does not exist yet and the server refuses the CREATE TABLE (1822 / errno 150): such a foreign key is added after the index creates instead, with the others. So is a foreign key of a new table whose columns an index the diff creates on that table covers: in the CREATE TABLE the server would make an index of its own for the constraint, and the CREATE INDEX would be a second one. And a DROP INDEX of an index a foreign key that stays still needs, with no other index left over the same columns, is refused by the server (1553): the index that replaces it, a create of the same diff over those columns, is created before the drop. When the replacement has the name of the index it replaces -- a changed index -- both are one statement, ALTER TABLE ... DROP INDEX n, ADD INDEX n (...) (action replace, with old_index_info). Where no index of the diff takes over, operations dies, naming the index and the foreign key; a statement the server is sure to refuse is not written.

The only key of an AUTO_INCREMENT column that is not the primary key is not one of them: such a column has to have a key, and the server refuses the DROP INDEX of it (1075). DBIO::MySQL::Diff puts the MODIFY COLUMN that takes AUTO_INCREMENT from the column -- or the one it writes for a column that leaves -- before that DROP INDEX.

METHODS

diff

my @ops = DBIO::MySQL::Diff::Index->diff(
  $source, $target, $source_tables, $target_tables);

The optional $source_tables / $target_tables hashrefs are the tables sections of the two models (threaded in by DBIO::MySQL::Diff, the same way it already threads them into Diff::Column / Diff::ForeignKey). They are used to detect tables that are being dropped in this same diff pass: DROP TABLE already removes that table's own indexes, so emitting a later standalone DROP INDEX ... ON <table> for one of them would fail against the server (the ON <table> target no longer exists), aborting the deploy. When these arguments are absent (e.g. direct unit-test calls) the set is empty and no drops are suppressed, preserving the original two-argument behaviour.

as_sql

The CREATE INDEX or DROP INDEX statement, or for the action replace the one ALTER TABLE that drops an index and adds the one of the same name. The names of the index, the table and the columns are quoted with "quote_ident" in DBIO::MySQL::Identifier.

summary

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.