--- title: "ext_tables.sql" manual: "TYPO3 Explained" version: "14.3" permalink: "https://docs.typo3.org/permalink/t3coreapi:ext-tables-sql@14.3" source: "ExtensionArchitecture/FileStructure/ExtTablesSql.rst" rendered: "2026-10-01T15:20:05+00:00" --- # `ext_tables.sql` {#ext-tables-sql} - **ext_tables.sql** - *Scope:* extension - *Path (Composer):* packages/my_extension/ext_tables.sql - *Path (Classic):* typo3conf/ext/my_extension/ext_tables.sql The `ext_tables.sql` file in the root folder of an extension holds additional SQL definition of database tables. This file should contain a table-structure dump of the tables used by the extension which are not [auto-generated](https://docs.typo3.org/permalink/t3coreapi:auto-generated-db-structure@14.3). It is used for evaluation of the database structure and is applied to the database when an extension is enabled. **Table of contents** - [Adding additional fields to existing tables](https://docs.typo3.org/permalink/t3coreapi:adding-additional-fields-to-existing-tables@14.3) - [Database types](https://docs.typo3.org/permalink/t3coreapi:database-types@14.3) - [Auto-generated structure](https://docs.typo3.org/permalink/t3coreapi:auto-generated-structure@14.3) ## Adding additional fields to existing tables {#ext-tables-sql-fields} If you add additional fields (or depend on certain fields) to existing tables you can also put them here. In this case insert a `CREATE TABLE` structure for that table, but remove all lines except the ones defining the fields you need. Here is an example adding a column to the pages table: ```sql CREATE TABLE pages ( tx_myextension_field int(11) DEFAULT '0' NOT NULL, ); ``` TYPO3 will merge this table definition to the existing table definition when comparing expected and actual table definitions (for example, via the **System > Maintenance > Analyze Database Structure** or the [CLI](https://docs.typo3.org/permalink/t3coreapi:symfony-console-commands@14.3) command `extension:setup`. Partial definitions can also contain indexes and other directives. They can also change existing table fields - but that is not recommended, because it may create problems with the TYPO3 Core and/or other extensions. The [`ext_tables.sql`](#file-extension-ext-tables-sql) file may not necessarily be "dumpable" directly to a database (because of the semi-complete table definitions allowed that define only required fields). But the modules **System > Extensions** or **System > Maintenance > Analyze Database Structure** can handle this. TYPO3 parses [`ext_tables.sql`](#file-extension-ext-tables-sql) files into a Doctrine DBAL object schema to define a virtual database scheme, enriched with `\TYPO3\CMS\Core\Database\Schema\DefaultTcaSchema` information for [TCA-managed, auto-generated](https://docs.typo3.org/permalink/t3coreapi:auto-generated-db-structure@14.3) tables and fields. Incorrect definitions may not be recognized by the TYPO3 SQL parser or may lead to SQL errors, when TYPO3 tries to apply them. The [`ext_tables.sql`](#file-extension-ext-tables-sql) file in TYPO3 contains SQL statements written in a TYPO3-specific format that is not directly valid for any database system. TYPO3 utilizes Doctrine DBAL to interpret and translate these statements into valid SQL for the specific target DBMS, such as MySQL, MariaDB, PostgreSQL, or SQLite. ## Database types {#ext-tables-sql-types} The following database types require special consideration if you use them: ### `CHAR` and `BINARY` as fixed length columns {#ext-tables-sql-types-fixed-length} Not all database systems (RDBMS) act the same way for fixed-length columns. Implementation differences need to be respected to ensure the same query/data behavior across all supported database systems. > [!WARNING] > Using fixed-length `CHAR` and `BINARY` column types requires to carefully work > with data being persisted and retrieved from the database due to differently > behavior specifically of PostgreSQL. > [!TIP] > `CHAR` and `BINARY` columns can be used (for storage or performance > adjustments), but only when composed data and queries take care of > database-system differences. > > Otherwise, the "safe bet" is to consistently utilize `VARCHAR` and > `VARBINARY` columns types. ### Fixed-length SQL type `CHAR` {#ext-tables-sql-types-char} #### Key difference between CHAR and VARCHAR {#ext-tables-sql-types-char-varchar-difference} The main difference between `CHAR` and `VARCHAR` is how the database stores character data in a database. `CHAR`, which stands for `character`, is a fixed-length data type, meaning it always reserves a specific amount of storage space for each value, regardless of whether the actual data occupies that space entirely. For example, if a column is defined as `CHAR(10)` and the word `apple` is stored inside of it, it will still occupy 10 characters worth of space (not just 5). Unused characters are padded with extra spaces. On the other hand, `VARCHAR`, short for `variable character`, is a variable-length data type. It only uses as much storage space as needed to store the actual data without padding. So, storing the word `apple` in a `VARCHAR(10)` column will only occupy 5 characters worth of space, leaving the remaining table row space available for other data. #### When to use `CHAR` columns {#ext-tables-sql-types-char-when} **Rule of thumb for fixed-length** `CHAR` **columns** - Only use with **ensured fixed-length values** (so that no padding occurs). - For 255 or more characters `VARCHAR` or `TEXT` must be used. #### Hints on using fixed-length `CHAR` columns {#ext-tables-sql-types-char-hints} - Ensure to write fixed-length values for `CHAR` (non-space characters), for example use hash algorithms which produce fixed-length hash identifier values. - Ensure to use query statements to `trim` OR `rightPad` the value within `WHERE`, `HAVING` or `SELECT` operations, when values are not guaranteed to contain fixed-length values. > [!TIP] > Helper `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder` > expressions can be used, for example > `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder->trim()` or > `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder->rightPad()` to. - Usage of `CHAR` **must** be avoided when using the column with the `Extbase ORM`, because fixed-value length cannot be ensured due to the lack of using `trim/rightPad` within the ORM generated queries. Only with ensured fixed-length values, it is usable with `Extbase ORM`. - Cover custom queries extensively with `functional tests` executed against all supported database platforms. Code within public extensions **should** ensure to test queries and their operations against all officially TYPO3-supported database platforms. Extended examples about how to handle `CHAR` columns can be found in [Important: #105310 - Create CHAR and BINARY as fixed-length columns](https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/14.0/Important-105310-CreateCHARAndBINARYAsFixedLengthColumns.html#important-105310-1736154829) ## Auto-generated structure {#auto-generated-db-structure} The database schema analyzer automatically creates TYPO3 "management"-related database columns by reading a table's TCA and checking the [Table properties (ctrl)](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/Ctrl/Index.html#ctrl) section for table capabilities. Field definitions in [`ext_tables.sql`](#file-extension-ext-tables-sql) take precedence over automatically generated fields, so the TYPO3 Core never overrides a manually specified column definition from an [`ext_tables.sql`](#file-extension-ext-tables-sql) file. > [!NOTE] > As column definitions are created automatically from the TCA configuration, > the [`ext_tables.sql`](#file-extension-ext-tables-sql) file can end up with a table definition without > columns, like: > > ```sql > CREATE TABLE tx_myextension_domain_model_table ( > ); > ``` > > This would be invalid as such in most > DBMS, since tables usually must have > at least one column. However, it is a valid definition in the scope of > [`ext_tables.sql`](#file-extension-ext-tables-sql) files when the TYPO3 Core enriches fields from TCA. > > Also, you can omit the `CREATE TABLE` statement without columns > entirely. These columns below are automatically added if not defined in [`ext_tables.sql`](#file-extension-ext-tables-sql) for database tables that provide a `$GLOBALS['TCA']` definition: - **`uid` and `PRIMARY KEY`** If the `uid` field is not provided inside the [`ext_tables.sql`](#file-extension-ext-tables-sql) file, the `PRIMARY KEY` constraint **must** be omitted, too. - **`pid` and `KEY parent`** The column `pid` is `unsigned`, if the table is not [workspace](https://docs.typo3.org/c/typo3/cms-workspaces/14.3/en-us/Index.html)-aware, the default index `parent` includes `pid` and `hidden` as well as `deleted`, if the latter two are specified in TCA's [Table properties (ctrl)](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/Ctrl/Index.html#ctrl). The parent index creation is only applied, if the column `pid` is auto-generated, too. The following [$GLOBALS\['TCA'\]\['ctrl'\]](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/Ctrl/Index.html#ctrl) are considered for auto-generated fields, if they are not manually defined in the [`ext_tables.sql`](#file-extension-ext-tables-sql) file: - **`['ctrl']['tstamp'] = 'my_field_name'`** Often set to `tstamp` or `updatedon`. - **`['ctrl']['crdate'] = 'my_field_name'`** Often set to `crdate` or `createdon`. - **`['ctrl']['delete'] = 'my_field_name'`** Often set to `deleted`. - **`['ctrl']['enablecolumns']['disabled'] = 'my_field_name'`** Often set to `hidden` or `disabled`. - **`['ctrl']['enablecolumns']['starttime'] = 'my_field_name'`** Often set to `starttime`. - **`['ctrl']['enablecolumns']['endtime'] = 'my_field_name'`** Often set to `endtime`. - **`['ctrl']['enablecolumns']['fe_group'] = 'my_field_name'`** Often set to `fe_group`. - **`['ctrl']['sortby'] = 'my_field_name'`** Often set to `sorting`. - **`['ctrl']['descriptionColumn'] = 'my_field_name'`** Often set to `description`. - **`['ctrl']['editlock'] = 'my_field_name'`** Often set to `editlock`. - **`['ctrl']['languageField'] = 'my_field_name'`** Often set to `sys_language_uid`. - **`['ctrl']['transOrigPointerField'] = 'my_field_name'`** Often set to `l10n_parent`. - **`['ctrl']['translationSource'] = 'my_field_name'`** Often set to `l10n_source`. - **`language_identifier` and `translation_source` indexes** Added for every language-aware table, based on the language [Table properties (ctrl)](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/Ctrl/Index.html#ctrl) configuration alone - independent of whether the fields above were added here or already declared in [`ext_tables.sql`](#file-extension-ext-tables-sql). The `language_identifier` index covers `['ctrl']['transOrigPointerField']` and `['ctrl']['languageField']`. If `['ctrl']['translationSource']` is set, the `translation_source` index additionally covers that field, so that both ways of matching a translation - by translation source, or by the translation origin pointer for translations saved without one - resolve through a single index. See [Important: #110454 - Translation source index covers the translation lookups](https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/13.4.x/Important-110454-TranslationSourceIndexCoversTranslationLookups.html#important-110454-1786550456). - **`l10n_state`** Column added if `['ctrl']['languageField']` and `['ctrl']['transOrigPointerField']` are set. - **`['ctrl']['origUid'] = 'my_field_name'`** Often set to `t3_origuid`. - **`['ctrl']['transOrigDiffSourceField'] = 'my_field_name'`** Often set to `l10n_diffsource`. - **`['ctrl']['versioningWS'] = true` and `t3ver_*` columns** Columns that make a table [workspace](https://docs.typo3.org/c/typo3/cms-workspaces/14.3/en-us/Index.html)-aware. All those fields are prefixed with `t3ver_`, for example `t3ver_oid`. A default index named `t3ver_oid` to fields `t3ver_oid` and `t3ver_wsid` is added, too. The configuration in [$GLOBALS\['TCA'\]\[$table\]\['columns'\]\[$field\]\['config'\]\['MM'\]](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/CommonProperties/Mm.html#tca_property_MM) is considered for auto-generating the intermediate table and fields for: - [TCA type "group"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Group/Index.html#columns-group-properties-mm) - [TCA type "inline"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Inline/Index.html#columns-inline-properties-mm) - [TCA type "select"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Select/Single/Index.html#columns-select-properties-mm) The following types configured via [$GLOBALS\['TCA'\]\[$table\]\['columns'\]\[$field\]\['config'\]](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Index.html#columns-types) are considered for auto-generated fields, if they are not manually defined in the [`ext_tables.sql`](#file-extension-ext-tables-sql) file: - [TCA type "category"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Category/Index.html#columns-category) - [TCA type "check"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Check/Index.html#columns-check) - [TCA type "color"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Color/Index.html#columns-color) - [TCA type "datetime"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Datetime/Index.html#columns-datetime) - [TCA type "email"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Email/Index.html#columns-email) - [TCA type "file"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/File/Index.html#columns-file) - [TCA type "flex"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Flex/Index.html#columns-flex) - [TCA type "folder"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Folder/Index.html#columns-folder) - [TCA type "group"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Group/Index.html#columns-group) - [TCA type "imageManipulation"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/ImageManipulation/Index.html#columns-imageManipulation) - [TCA type "inline"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Inline/Index.html#columns-inline) - [TCA type "json"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Json/Index.html#columns-json) - [TCA type "language"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Language/Index.html#columns-language) - [TCA type "link"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Link/Index.html#columns-link) - [TCA type "number"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Number/Index.html#columns-number) - [TCA type "password"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Password/Index.html#columns-password) - [TCA type "radio"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Radio/Index.html#columns-radio) - [TCA type "select"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Select/Index.html#columns-select) - [TCA type "slug"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Slug/Index.html#columns-slug) - [TCA type "text"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Text/Index.html#columns-text) - [TCA type "uuid"](https://docs.typo3.org/m/typo3/reference-tca/14.3/en-us/ColumnsConfig/Type/Uuid/Index.html#columns-uuid)