Table naming conventions

This topic describes the table naming conventions to help you standardize table names when you create tables in database operations.

Recommended conventions

• Table names must conform to the following rules in MySQL mode and Oracle mode:

  • General rules for OceanBase Database in MySQL mode

    • Table names must consist of lowercase letters, numbers, and underscores. They must start with a lowercase letter and end with a lowercase letter or a number.
    • Table names must be at least 3 characters long (development specifications) and less than the maximum allowed length for database table names.
    • Table names must not be keywords or reserved words in MySQL mode of OceanBase Database.

    Notice

    The maximum length of a table name in MySQL mode of OceanBase Database is 64 characters. The actual limit may vary by version.

  • General rules for OceanBase Database in Oracle mode

    • Table names must consist of uppercase letters, numbers, and underscores. They must start with an uppercase letter and end with an uppercase letter or a number.
    • Table names must be at least 3 characters long (development specifications) and less than the maximum allowed length for database table names.
    • Table names must not be keywords or reserved words in Oracle mode of OceanBase Database.

    Notice

    • Except for the case sensitivity requirement, the rules for table names in Oracle mode of OceanBase Database are the same as those in MySQL mode.
    • The maximum length of a table name in Oracle mode of OceanBase Database is 64 characters. The actual limit may vary by version.

• Start table names with letters.

• Use table names that well represent the data content, for example, "TEST".

• Do not start or end table names with underscores (_).

• Do not start table names with digits.

• Do not use reserved words or keywords in table names.

• Do not enclose digits in underscores (_) in table names.

• Do not use plural forms in table names.

• Start a table name with a subsystem name or its standard abbreviation, followed by "_" and then a feature name or its standard abbreviation in the format of "business name_table feature", for example, "ACCOUNT_USER".

• Add a number that increments progressively starting from 00 at the end of a table name, for example, ACCOUNT_USER_00, ACCOUNT_USER_01, and ACCOUNT_USER_02.

• For tables partitioned by time, follow the table naming format of "general table name_time. For example, name a table partition that is of the "ACCOUNT_USER" table and that is obtained through partitioning in January 2022 as "ACCOUNT_USER_2201".

• For an intermediate table that stores intermediate result sets, follow the table naming format of "tmp_ table name (or abbreviation)_column name( or abbreviation)_creation time", for example, "tmp_account_tbluser_20220224".

• For a backup table that stores backups or snapshots of a source table, follow the table naming format of "bak_table name (or abbreviation)_column name (or abbreviation)_creation time", for example, "bak_account_tbluser_20220224".