Database columns generated from TCA 

TYPO3 builds the expected database schema automatically from the TCA definitions.

Technical fields mentioned in the Table properties (ctrl) are created automatically.

Field definitions (columns) of most field types also create their database schema automatically.

If you have to override the default SQL types you can define them in the ext_tables.sql.

For field types that are not supported, a definition in ext_tables.sql is mandatory.

Table of contents

Overriding database schema in ext_tables.sql 

A column defined in ext_tables.sql is never overwritten. Define a column there to change its SQL type, its length, or its default.

Most tables need no column definition at all. The CREATE TABLE statement can stay empty, or you can leave it out:

EXT:my_extension/ext_tables.sql 
CREATE TABLE tx_myextension_conference (
);
Copied!

The missing columns are created in System > Maintenance > Analyze Database Structure and by the command typo3 extension:setup .

Columns created from the ctrl section 

Settings like enablecolumns, languageField, or sortby name a technical field. That field is created automatically.

The uid and pid columns are created automatically as well, together with the indexes of a language-aware table and the t3ver_* columns of a workspace-aware table.

Auto-generated structure in TYPO3 Explained lists all ctrl settings that create a column.

Columns created from the column types 

Each entry below gives the column as it is created on MySQL and MariaDB, followed by the Doctrine DBAL type it is declared with. PostgreSQL and SQLite get a column of that same Doctrine type, written in their own way.

The types none, passthrough, and user create no column. Define a field of those types in ext_tables.sql.

nullable changes most of these columns: they lose their NOT NULL and their default becomes NULL, for example varchar(255) DEFAULT NULL instead of varchar(255) DEFAULT '' NOT NULL .

category

int unsigned DEFAULT 0 NOT NULL , Doctrine type integer. The column counts the related records.

With relationship set to oneToMany it is a longtext DEFAULT NULL , Doctrine type text, with the list of uids.

check
smallint unsigned DEFAULT 0 NOT NULL , Doctrine type smallint. The TCA default becomes the default of the column.
color

varchar(7) DEFAULT '' NOT NULL , Doctrine type string, long enough for a value like #ff8700.

With opacity enabled it is a varchar(9) , which also holds the alpha value.

country
varchar(16) DEFAULT NULL , Doctrine type string. The field stores an ISO 3166-1 alpha-2 code like DE. The remaining length is for items added by an itemsProcFunc.
datetime

bigint DEFAULT 0 NOT NULL , Doctrine type bigint, holding a UNIX timestamp. The column is signed and covers dates before 1970 and after 2038.

With dbType the column gets the native type of that name: date , datetime , or time .

email
varchar(255) DEFAULT '' NOT NULL , Doctrine type string.
file
int unsigned DEFAULT 0 NOT NULL , Doctrine type integer. The column counts the file references. The references themselves are stored in sys_file_reference.
flex
longtext DEFAULT NULL , Doctrine type text, holding the XML of the FlexForm.
folder
longtext DEFAULT NULL , Doctrine type text.
group

longtext DEFAULT NULL , Doctrine type text, with the list of references.

With MM the column counts the related records instead: int unsigned DEFAULT 0 NOT NULL , Doctrine type integer.

imageManipulation
longtext DEFAULT NULL , Doctrine type text.
inline

int unsigned DEFAULT 0 NOT NULL , Doctrine type integer. The column counts the children. This applies to every relation with foreign_field or MM.

A relation that keeps the uids in the parent gets a varchar(255) DEFAULT '' NOT NULL with a comma-separated list, Doctrine type string.

The child table gets columns too. foreign_field becomes an int unsigned DEFAULT 0 NOT NULL , foreign_table_field a varchar(255) DEFAULT '' NOT NULL . Both are created only if the TCA of the child does not define the field, or defines it as passthrough.

input
varchar(255) DEFAULT '' NOT NULL , Doctrine type string. A max of its own becomes the length, for example varchar(40) . Above 255 the column is a text DEFAULT '' NOT NULL instead, Doctrine type text.
json
json DEFAULT NULL , Doctrine type json.
language
int DEFAULT 0 NOT NULL , Doctrine type integer. The column is signed because -1 stands for "all languages".
link
text DEFAULT '' NOT NULL , Doctrine type text.
number

int DEFAULT 0 NOT NULL , Doctrine type integer. The column is unsigned if range has a lower of 0 or higher.

A scale above 0 gives a decimal , Doctrine type decimal. It has eight digits in front of the decimal point plus the scale: a scale of 2 gives decimal(10,2) DEFAULT 0 NOT NULL , a scale of 6 gives decimal(14,6) . SQLite stores such a value as a string so that it is not rounded.

password
varchar(255) DEFAULT '' NOT NULL , Doctrine type string.
radio

varchar(255) DEFAULT '' NOT NULL , Doctrine type string.

If the values of items are all integers the column is a smallint DEFAULT 0 NOT NULL , Doctrine type smallint. Values outside -32768 to 32767 give an int DEFAULT 0 NOT NULL , Doctrine type integer.

select

varchar(255) DEFAULT '' , Doctrine type string, with a comma-separated list of the selected values. A dbFieldLength of its own replaces the length of 255.

A field that stores a single value, which is the renderType selectSingle or a maxitems of 1, gets an int DEFAULT 0 NOT NULL for integer values and a varchar(255) DEFAULT '' NOT NULL for other values. The column is unsigned if no value is negative. It allows NULL if an item has the value null .

With MM the column counts the related records: int unsigned DEFAULT 0 NOT NULL , Doctrine type integer.

In all other cases the column is a longtext DEFAULT NULL , Doctrine type text.

slug
text DEFAULT NULL , Doctrine type text.
text
longtext DEFAULT NULL , Doctrine type text.
uuid
char(36) NOT NULL , Doctrine type guid. A UUID in its usual notation has 36 characters.

The intermediate table of an MM relation 

A MM setting on a field of the type category, group, inline, or select also creates the intermediate table. That table has no TCA and no ext_tables.sql entry of its own. Auto creation of intermediate MM tables from TCA describes when it is created and what it is for.

Its columns are:

uid_local and uid_foreign
int unsigned DEFAULT 0 NOT NULL . The first points at the record that holds the field, the second at the related record. uid_foreign always gets an index. uid_local gets one only together with the uid column below, because the primary key otherwise starts with uid_local and indexes it already.
sorting and sorting_foreign
int unsigned DEFAULT 0 NOT NULL , the order of the records on each side of the relation. Both are always created.
tablenames
varchar(64) DEFAULT '' NOT NULL . A group field with prepend_tname gets this column, and so do the two cases below.
fieldname
varchar(64) DEFAULT '' NOT NULL . It is created together with tablenames if one intermediate table serves several fields: with MM_oppositeUsage, and for a group field whose allowed names more than one table or is *.
uid
int unsigned AUTO_INCREMENT NOT NULL , created only with multiple. That setting lets the same record take part in the relation twice, which the usual primary key forbids.

The primary key is uid_local and uid_foreign, extended by tablenames and fieldname if both exist. With multiple the uid column is the primary key instead.

What to watch out for 

A ctrl setting in an override file creates nothing 

A setting that creates a column is read from the base file Configuration/TCA/<table>.php only. The same setting in Configuration/TCA/Overrides/ comes too late and leaves you without the column. See Load order when building TCA.

An old definition keeps its old column 

A column defined in ext_tables.sql stays as it is defined. An extension written before TYPO3 created these columns therefore keeps every column it defined, also those that no longer match. Delete the definition to get the automatic column. Read the statements of System > Maintenance > Analyze Database Structure before you apply them: a change from one type to another can cut values short.

A changed TCA type changes the column 

The automatic column follows the TCA. A select field that stored integer values and now stores strings turns from an int into a varchar . The database converts the stored values, not TYPO3. Check what that conversion does to the existing rows before you apply it.