A foreign key's implicit index breaks dropping its column after introspection

[morozov]

Q	A
Version	4.4.x

Table fabricates an implicit index to back a foreign key and records its name in $implicitIndexNames. On a constructed table the index is implicit, so dropping the foreign key drops it too. A round-trip through the database breaks that in two steps:

1. Create-and-introspect loses the index's implicitness. The database has no notion of an implicit index, so introspection brings it back as an explicit one.
2. Dropping the foreign key does not automatically drop the no longer implicit index. Dropping its column then leaves an index over a column that doesn't exist, so the table is invalid.

Given child(id PK, parent_id) with a foreign key child_parent on parent_id referencing parent(id), where the invalid table is caught depends on how it's edited:

• The mutable Table API has no build-time check, so the invalid table reaches the database on drop-and-create:

  $child = $schemaManager->introspectTableByUnquotedName('child');
  $child->removeForeignKey('child_parent');
  $child->dropColumn('parent_id');
  $schemaManager->dropTable('child');
  $schemaManager->createTable($child);
  // DriverException: SQLSTATE[HY000]: General error: 1 no such column: parent_id

• The editor rejects it earlier, when create() builds the object:

  $child = $schemaManager->introspectTableByUnquotedName('child');
  $child->edit()
      ->dropColumnByUnquotedName('parent_id')
      ->dropForeignKeyConstraintByUnquotedName('child_parent')
      ->create();
  // ColumnDoesNotExist: There is no column with name "parent_id" on table child.

[UmairRathore]

I reproduced this and traced the implicit-index provenance loss through introspection. I initially considered removing indexes referencing a dropped column in Table::dropColumn(), but that changes the mutable Table API's existing non-cascading behavior and differs from TableEditor.

I also looked at the newer SQLite reconstruction work (#7392 / #7484). The relevant filtering is already present in 4.4.x, and it cannot cover the direct dropTable() / createTable() reproducer since no TableDiff is involved.

After introspection there doesn't appear to be a reliable way to distinguish an originally implicit FK backing index from an explicitly user-created index that happens to fulfill the FK. Would you expect this to be addressed through additional provenance/metadata, validation of invalid Table state, or some other API behavior?

[morozov]

The corresponding WIP is #7477 — instead of making up "implicit indexes" in the model definition, I want the DBAL to account for "derived objects", which are:

1. Indexes backing foreign key and unique constraints
2. Unique constraints that MySQL automatically declares for unique indexes.

The current state of the PR is a bit of a dead end — it assumed that this comparison is possible w/o knowing the provenance of a given object (e.g. index). This is true for MySQL but isn't true for SQLite and IIRC some other databases, so it needs to be redesigned.