Table naming conventions

This topic aims to help developers standardize table naming in the database.

Table naming

• Table naming rules for MySQL-compatible mode and Oracle-compatible mode are as follows:

  • General naming rules for OceanBase Database in MySQL-compatible mode

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

    Notice

    The maximum length for a table name in MySQL-compatible mode of OceanBase Database is 64 characters. This may vary depending on the specific version.

  • General naming rules for OceanBase Database in Oracle-compatible mode

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

    Notice

    • Except for the capitalization requirements, the naming rules for OceanBase Database in Oracle-compatible mode are the same as those in MySQL-compatible mode.
    • The maximum length for a table name in Oracle-compatible mode of OceanBase Database is 64 characters. This may vary depending on the specific version.

• Use letters to start table names.

• Use table names that clearly indicate the content, for example, "TEST".

• Do not start or end table names with underscores.

• Do not start table names with digits.

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

• Do not use only digits between underscores in table names.

• Do not use plural nouns for table names.

• Start a table name with a subsystem name or its standard abbreviation, followed by a feature name or its standard abbreviation, separated by "_". The recommended format is "business name_table purpose", for example, "ACCOUNT_USER".

• For numeric suffixes, use sequential numbers starting from "00", for example, "ACCOUNT_USER_00", "ACCOUNT_USER_01", "ACCOUNT_USER_02".

• For time-based partitioned tables, use the format "table name_time", where the time is a 4- to 6-digit abbreviation of the data time. For example, name the January 2022 partition of the "ACCOUNT_USER" table "ACCOUNT_USER_2201".

• For intermediate tables that store intermediate result sets, use the format "tmp_table name (or abbreviation)_column name (or abbreviation)_creation time", for example, "tmp_account_tbluser_20220224".

• For backup tables used for backup or snapshot of a source table, use the format "bak_table name (or abbreviation)_column name (or abbreviation)_creation time", for example, "bak_account_tbluser_20220224".