#105438Setting a foreign key on an TCA-autogenerated SQL column crashes the DB compare tool
Description
When having an inline field or a select field to another table, it can be useful to declare foreign keys on the relation field (for reeferential integrity, generating SQL relation diagrams or doing ON DELETE/@ON UPDATE@ stuff directly via SQL). When letting the TCA autogenerate the SQL definition it is not possible to declare a foreign key. The DB compare tool will just crash hard. One has to copy the field definition to ext_tables.sql then.
# When letting this field be created by TCA automagic (same SQL defintion) it will crash:
my_field int unsigned DEFAULT NULL,
FOREIGN KEY fk_my_field (my_field) REFERENCES foreign_table (uid)
Note: this bug only affects foreign keys! Normal indexes work just fine on TCA-autogenerated fields!
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
Fix this issue
Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch
- Commit message
-
[BUGFIX] Setting a foreign key on an TCA-autogenerated SQL column crashes the DB compare tool Resolves: #105438 Releases: main
History 4
-
Stefan Bürk commented and changed status
NewNeeds Feedback assigned to Stefan Bürk <span data-processor="relativetime" data-value="2025-01-07T14:04:24+00:00" > 01/07/25, 14:04 </span> -
S P commented <span data-processor="relativetime" data-value="2025-01-09T09:18:59+00:00" > 01/09/25, 09:18 </span>
Add this to TCA tt_content.php, so that the column would get autocreated :
$GLOBALS['TCA']['tt_content']['columns']['forge_test_page'] = [ 'config' => [ 'type' => 'select', 'renderType' => 'selectSingle', 'foreign_table' => 'pages' ] ];BEFORE adding the actual row to the DB, also add this to
ext_tables.sql:CREATE TABLE tt_content ( FOREIGN KEY forge_test_105438 (forge_test_page) REFERENCES pages (uid) );Then go to Install Tool / Maintenance, Analyze Database Structure.It will directly crash with this error (without showing any possible DB schema changes):
Uncaught TYPO3 Exception: There is no column with name "`forge_test_page`" on table "tt_content".
This error comes from Doctrine DBAL! So it seems, TYPO3 does not pass all needed Table Diff configurations to Doctrine DBAL.
If you then explicitly additionally add the line
forge_test_page INT UNSIGNED DEFAULT 0 NOT NULL,to ext_tables.sql as well ( instead of using autocreation ) the schema inspector will not crash.
This only happens with foreign keys, not with normal indexes.
It seems the Schema Inspector loads the maybe-to-be- autogenerated columns to late. Or the maybe-to-add foreign keys to early. So that they are not passed together to the same Doctrine DBAL Diff.
-
Stefan Bürk commented <span data-processor="relativetime" data-value="2026-04-14T21:33:48+00:00" > 04/14/26, 21:33 </span>
Not thats fails already reading `ext_tables.sql` files or geting the
SQL Snippet based fragements from the PSR-14 events, which is parsed
first.It never reaches the database compare logic or the DefaultTcaSchema
stuff at all.TableBuilder (part of the SQLReader part) creates Doctrine DBAL schema
classes based on the “lexer internal meta classes” and the exception is
thrown by Doctrine when a index should be added to aTableobject and
no matching column exists in thatTableobject.So no easy and elagant option here and a quick win – I guess that would
be something as part of a complete overhaul of the whole procedure and
build up :/ -
S P commented and changed TYPO3 Version
1314 <span data-processor="relativetime" data-value="2026-06-11T07:05:12+00:00" > 06/11/26, 07:05 </span>Bug/inconsistent behaviour (crash for foreign key, but no crash for normal keys) still present on v14.
@stefanp Can you please provide some more context like the used TCA for both tables / ext_tables.sql so it
can be used to verify / debug this and additional create an testcase out of it when this is accepted ?
Eventualy as a smal “proof-of-concept” public extension ? Nothing fancy, just the basic broken
configuation as a public github repository ? (composer.json / Configuration/Tca(/Overrides) ?