[Database_CreateField] creates a new field in an existing MySQL database table. By calling the [Database_CreateTable] tag and a series of [Database_CreateField] tags a new table can be created entirely in LassoScript.

This tag is certified to work with Lasso MySQL and MySQL data sources. It may also work with third party data sources that support the -SQL command tag.

This tag requires -Database and -Table parameters that specify in which database and table the field should be created. The database and table must already exist and the current user must have permission to execute -SQL actions. The database and table must be specified using the proper capatilization in order for this tag to work properly.

The -Field parameter specifies the name of the field to be created and the -Type parameter specifies the type for the new field. The -Type parameter must be set to a valid MySQL type. The valid types are summarized below.

An optional default value for the field can be specified in the -Default parameter. If the field is not set to an explicit value when a new record is added then this value will be used for the field.

The field is added to the end of the table by default. The -BeforeFirst parameter can be optionally specified to create the field at the start of the table. Or, the -AfterField parameter can be specified to name an existing field after which the field should be added.

Key fields can be created by specifying the option -Key parameter. In general only one key field can be created. Similarly, an integer field can be set to auto increment using the -AutoIncrement parameter. In general only one field can be set to auto increment.

A field can be set to accept NULL values by specifying the -Null parameter or can be set not to accept NULL values by specifying the -NotNull parameter.

The valid MySQL data types are as follows:

Integers: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Each integer type can also be unsigned as in TINYINT UNSIGNED.

Decimals: FLOAT, DOUBLE, DECIMAL(Length, Precision).

Strings: CHAR(Length), VARCHAR(Length), TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT.

Binary: CHAR(Length) BINARY, VARCHAR(Length) BINARY, TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB.

Dates: DATETIME, TIMESTAMP, DATE, TIME, YEAR.

[Database_CreateField: -Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -Default='Default Value']

[Database_CreateField: -Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -BeforeFirst,
  -Null]

[Database_CreateField: -Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -AfterField='Field Name',
  -Key,
  -AutoIncrement,
  -NotNull]

To create a new field in a Lasso MySQL table:

Use the [Database_CreateField] tag. The following code creates a new field in the MyPreferences table of the Site database. The new field is named CreationDate and is set to data type DATETIME.

[Database_CreateField: -Database='Site',
  -Table='MyPreferences',
  -Field='CreationDate',
  -Type='DATETIME']