Schema Definition Language
MySQL Schema Definition Language Syntax
CREATE TABLE statements
Syntax
CREATE TABLE [IF NOT EXISTS] table_name (
{
column_name column_definition
| PRIMARY KEY (key_part, ...) [index_option]
| CONSTRAINT constraint_name FOREIGN KEY (column_name, ...) reference_definition
| CONSTRAINT constraint_name CHECK (expr) [[NOT] ENFORCED]
}
)
[table_options]Description
CREATE TABLE defines a table in the database. Unlike the regular CREATE TABLE statement in MySQL, in MySQL SDL, only columns, PRIMARY KEY, FOREIGN KEY constraints, and CHECK constraints can be defined.
Parameters
table_name
- The name of the table.
column_name
- The name of the column.
column_definition
- The column definition. In MySQL SDL, PRIMARY KEY, FOREIGN KEY constraints and CHECK constraints cannot be defined in column_definition. They can only be defined separately.
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[COMMENT 'string']
}key_part
key_part: {col_name [(length)] | (expr)} [ASC | DESC]index_option
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
| ENGINE_ATTRIBUTE [=] 'string'
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}reference_definition
- The reference definition of a FOREIGN KEY. Note that the name of FOREIGN KEY constraints MUST be explicitly specified.
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULTCHECK Constraints
- The CHECK constraint definition. Note that the name of CHECK constraints MUST be explicitly specified.
table_options
- The table options definition.
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| START TRANSACTION
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| tablespace_option
}Example
CREATE TABLE book(
id INT NOT NULL,
name VARCHAR(20),
author_id INT,
-- PRIMARY KEY must be defined separately.
PRIMARY KEY (id),
-- FOREIGN KEY constraint must be defined separately.
-- The name of FOREIGN KEY constraint must be explicitly specified.
CONSTRAINT fk_id_author_id FOREIGN KEY (author_id) REFERENCES author(id),
-- CHECK constraint must be defined separately.
-- The name of CHECK constraint must be explicitly specified.
CONSTRAINT check_id CHECK (id > 0)
);CREATE INDEX statements
Syntax
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...Description
CREATE INDEX defines an index. Unlike the regular CREATE TABLE statement in MySQL, in MySQL SDL, indexes and unique indexes can only be defined by CREATE INDEX statements. MySQL SDL does not allow the definition of index and unique index in CREATE TABLE statements.
Parameters
index_name
- The name of index.
index_type
- The type of index.
index_type:
USING {BTREE | HASH}key_part
key_part: {col_name [(length)] | (expr)} [ASC | DESC]index_option
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
| ENGINE_ATTRIBUTE [=] 'string'
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}algorithm_option
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}lock_option
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}Example
CREATE UNIQUE INDEX uk_book_id_name on book(id, name);