Defining Tables for MySQL

Overview

Tables are defined via JSON files in the tables folder of your product in the form <Table name.json>. After a little use, the format will feel very comfortable. Arrays of objects are used to define collections like Columns, Indexes and ForeignKeys. Each object in the collection has the properties you would expect.

Columns have required properties like Name and DataType, and optional properties like Nullable or Default. Indexes and Foreign Keys are similar. MySQL uses backtick quoting for identifiers that contain reserved words or special characters.

Example

A complete table definition showing columns, indexes, and AUTO_INCREMENT. View the MySQL Enterprise Demos on GitHub .

{
  "Name": "actor",
  "Engine": "InnoDB",
  "Columns": [
    {
      "Name": "actor_id",
      "DataType": "INT",
      "Nullable": false,
      "AutoIncrement": true,
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "first_name",
      "DataType": "VARCHAR(45)",
      "Nullable": false,
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "last_name",
      "DataType": "VARCHAR(45)",
      "Nullable": false,
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "last_update",
      "DataType": "TIMESTAMP",
      "Nullable": false,
      "Default": "CURRENT_TIMESTAMP",
      "OnUpdate": "CURRENT_TIMESTAMP",
      "ShouldApplyExpression": "",
      "OldName": ""
    }
  ],
  "Indexes": [
    {
      "Name": "PRIMARY",
      "PrimaryKey": true,
      "Unique": true,
      "IndexColumns": "actor_id",
      "ShouldApplyExpression": ""
    },
    {
      "Name": "idx_actor_last_name",
      "PrimaryKey": false,
      "Unique": false,
      "IndexColumns": "last_name",
      "ShouldApplyExpression": ""
    }
  ],
  "ForeignKeys": [],
  "CheckConstraints": [],
  "ShouldApplyExpression": "",
  "ContentFile": "Table Contents/actor.tabledata",
  "MergeType": "Insert/Update",
  "OldName": ""
}

Supported Concepts

Storage engine (InnoDB, MyISAM, etc.) and row format
Table-level character set and collation
AUTO_INCREMENT seed value
AUTO_INCREMENT columns
Should Apply Expressions
All column data types
Column expressions, defaults, and ON UPDATE expressions
Generated columns (VIRTUAL and STORED)
Column-level character set and collation
Indexes (BTREE, HASH, FULLTEXT, SPATIAL) with visibility control
Full-text indexes with parser support (ngram, mecab)
Foreign keys
Table level check constraints
Custom Table Properties fully supported at the table level and all components
Data Delivery

Tip

If you do not require a property, for example, character set or collation, do not include it in the definition. In most cases, as long it is not something required, like name or data type, you can reasonably expect it to default as you would think.

Renaming Tables

To rename a table, first, rename your table json file to match your new table name. Within the metadata file add a property named OldName with the current name. Finally, change the Name property to be your new name. SchemaQuench will use this to rename the table when the current name does not yet exist and the old name is still present. After the rename has occurred in all of your environments, you can safely remove the OldName property.

Renaming Columns

Similar to renaming a table, add an OldName property with the current column name. Change the Name property to your new column name. After SchemaQuench has run in all of your environments and the rename has been rolled out, you can safely remove the OldName property.

Tip

SchemaHammer does this for you behind the scenes when you to do either a table or column rename.

Should Apply Expression

You can define an expression that must be satisfied in order to deploy a given table or component. This can be useful for things like specific version requirements for an index or data type. You could define the same index or column in two ways specific to the server version or compatibility level and set the expression so that only the supported one is deployed on the version of the server being updated. You could use the expression to define environment-specific or even customer-specific indexing or tables. You could also use this feature to define items you want to track for documentation or custom attribute purposes but not actually apply to your database.

Property Reference

Complete reference for all table definition components. Each section documents the JSON properties available for that component type.

Table Properties

Property	Type	Description
`Name`	string	Table name Required
`Engine`	string	Storage engine: `InnoDB` (default), `MyISAM`, `MEMORY`, `CSV`, `ARCHIVE`, etc.
`RowFormat`	string	Row storage format: `DYNAMIC`, `COMPACT`, `COMPRESSED`, or `REDUNDANT`
`CharacterSet`	string	Table-level character set (e.g., `utf8mb4`)
`Collation`	string	Table-level collation (e.g., `utf8mb4_unicode_ci`)
`Comment`	string	Table comment (up to 2048 characters)
`AutoIncrementValue`	integer	Set the next AUTO_INCREMENT value for the table. Minimum 1.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this table. See Should Apply Expression.
`OldName`	string	Previous table name for renaming. See Renaming Tables.
`ContentFile`	string	Path to the data file for data delivery. Supports CSV and JSON formats.
`MergeType`	string	How data is merged: `MergeInsertOnly`, `MergeInsertUpdate`, `MergeInsertUpdateDelete`, or `Truncate`. See Data Delivery.
`MatchColumns`	string	Comma-separated columns used to match source rows to target rows during merge.
`MergeFilter`	string	WHERE clause to limit which target rows are affected by the merge.
`MergeDisableTriggers`	boolean	Disable triggers during data delivery. Defaults to `false`.

Column Properties

Property	Type	Description
`Name`	string	Column name Required
`DataType`	string	MySQL data type (e.g., `INT`, `VARCHAR(50)`, `DATETIME`, `TEXT`) Required
`Nullable`	boolean	Allow NULL values. Defaults to `false`.
`Default`	string	Default value expression (e.g., `CURRENT_TIMESTAMP`, `0`, `'N/A'`)
`AutoIncrement`	boolean	Enable AUTO_INCREMENT for this column. Only one column per table can be auto-increment.
`OnUpdate`	string	ON UPDATE expression (e.g., `CURRENT_TIMESTAMP`). Typically used with TIMESTAMP or DATETIME columns.
`CharacterSet`	string	Column-level character set override (e.g., `utf8mb4`)
`Collation`	string	Column-level collation override (e.g., `utf8mb4_unicode_ci`)
`Comment`	string	Column comment
`Generated`	string	Generation type: `VIRTUAL` (computed on read) or `STORED` (materialized on write)
`GenerationExpression`	string	Expression for generated columns (e.g., `CONCAT(first_name, ' ', last_name)`)
`ShouldApplyExpression`	string	Conditional expression to include/exclude this column. See Should Apply Expression.
`OldName`	string	Previous column name for renaming. See Renaming Columns.

Index Properties

Property	Type	Description
`Name`	string	Index name. Use `PRIMARY` for the primary key. Required
`PrimaryKey`	boolean	Whether this index is the primary key.
`Unique`	boolean	Enforce uniqueness on the indexed columns.
`UniqueConstraint`	boolean	Whether this is a unique constraint (as opposed to a unique index).
`IndexType`	string	Index algorithm: `BTREE` (default), `HASH`, `FULLTEXT`, or `SPATIAL`
`IndexColumns`	string	Comma-separated list of indexed columns (e.g., `last_name,first_name`) Required
`Visible`	boolean	Index visibility to the optimizer. Defaults to `true`. Set to `false` to make the index invisible (MySQL 8.0+). Useful for testing index removal impact.
`Comment`	string	Index comment
`ShouldApplyExpression`	string	Conditional expression to include/exclude this index.

Full-Text Index Properties

Full-text indexes are defined separately from regular indexes and support custom parsers for CJK and other languages.

Property	Type	Description
`Name`	string	Full-text index name Required
`Columns`	string	Comma-separated columns to include in the full-text index Required
`Parser`	string	Full-text parser plugin: `ngram` (for CJK languages), `mecab` (Japanese morphological analysis), or omit for the default parser.
`Comment`	string	Full-text index comment
`ShouldApplyExpression`	string	Conditional expression to include/exclude this full-text index.

Foreign Key Properties

Property	Type	Description
`Name`	string	Foreign key constraint name Required
`Columns`	string	Comma-separated columns in this table Required
`RelatedTable`	string	Referenced table name Required
`RelatedColumns`	string	Comma-separated columns in the referenced table Required
`DeleteAction`	string	Referential action on delete: `RESTRICT`, `CASCADE`, `SET NULL`, `NO ACTION`, `SET DEFAULT`
`UpdateAction`	string	Referential action on update: `RESTRICT`, `CASCADE`, `SET NULL`, `NO ACTION`, `SET DEFAULT`
`ShouldApplyExpression`	string	Conditional expression to include/exclude this foreign key.

Check Constraint Properties

Property	Type	Description
`Name`	string	Constraint name Required
`Expression`	string	CHECK expression (e.g., `price > 0`) Required
`ShouldApplyExpression`	string	Conditional expression to include/exclude this constraint.

Additional Resources

Forge Deeper with SchemaQuench. Deep dive Product Demos - Sample databases converted to the SchemaSmith metadata format. Examples Product Folder Structure - See the basic layout on disk for your product. Reference Custom Properties - Extend your domain seamlessly into your model. Reference Data Delivery - Making data deployment easier. Reference

Also available for SQL Server and PostgreSQL

Defining Tables - MySQL (Enterprise)