ext_tables.sql

-- optional

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. It is used for evaluation of the database structure and is applied to the database when an extension is enabled.

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:

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 Admin Tools > Maintenance > Analyze Database Structure or the CLI 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 may not necessarily be "dumpable" directly to a database (because of the semi-complete table definitions allowed that define only required fields). But the extension manager or admin tools can handle this.

TYPO3 parses ext_tables.sql files. TYPO3 expects that all table definitions in this file look like the ones produced by the mysqldump utility. Incorrect definitions may not be recognized by the TYPO3 SQL parser or may lead to SQL errors, when TYPO3 tries to apply them. If TYPO3 is not running on MySQL or a directly compatible other DBMS like MariaDB, the system will parse the file towards the target DBMS like PostgreSQL or SQLite.

Auto-generated structure

The database schema analyzer automatically creates TYPO3 "management"-related database columns by reading a table's TCA and checking the Table properties (ctrl) section for table capabilities. Field definitions in 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.

Note

Changed in version 13.0: A CREATE TABLE statement without columns in the ext_tables.php file is supported.

As column definitions are created automatically, the ext_tables.sql file can end up with a table definition without columns, like:

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 files when the TYPO3 Core enriches fields from TCA.

These columns below are automatically added if not defined in 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, the PRIMARY KEY constraint must be omitted, too.

pid and KEY parent

The column pid is unsigned, if the table is not workspace-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). The parent index creation is only applied, if the column pid is auto-generated, too.

The following $GLOBALS['TCA']['ctrl'] are considered for auto-generated fields, if they are not manually defined in the 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.

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-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 following $GLOBALS['TCA'][$table]['columns'][$field]['config'] are considered for auto-generated fields, if they are not manually defined in the ext_tables.sql file:

['config']['MM']

CREATE TABLE definitions for intermediate tables referenced by TCA table columns should not be defined manually in the ext_tables.php file:

['config']['type'] => 'slug'

New in version 12.0: Database table fields for TCA type slug are created automatically.

See also Slugs / URL parts.