Issue Bug Needs Feedback Stale

#105438Setting a foreign key on an TCA-autogenerated SQL column crashes the DB compare tool

S P 10/24/24, 13:00 updated 06/11/26, 07:05

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

  1. Stefan Bürk commented and changed status New Needs 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>

    @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) ?

  2. 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.

  3. 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 a Table object and
    no matching column exists in that Table object.

    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 :/

  4. S P commented and changed TYPO3 Version 13 14 <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.