Schema Specification

[Mandatory][Style] Table naming conventions

Description

Table naming conventions in the database (physical and logical):

• General OceanBase Database MySQL Mode naming conventions

  • Composed of lowercase letters, numbers, and underscores, starting with a lowercase letter and ending with a lowercase letter or number.
  • Length must be at least 3 characters (development specifications) and less than the maximum allowed length for database table names.
  • Must not contain keywords or reserved words in OceanBase Database MySQL Mode.

  Notice

  The maximum length for a table name in OceanBase Database MySQL Mode is currently 64 characters. This may vary by specific version.

• General OceanBase Database Oracle Mode naming conventions

  • Composed of uppercase letters, numbers, and underscores, starting with an uppercase letter and ending with an uppercase letter or number.
  • Length must be at least 3 characters (development specifications) and less than the maximum allowed length for database table names.
  • Must not contain keywords or reserved words in OceanBase Database Oracle Mode.

  Notice

  • Except for the case sensitivity of letters, the naming conventions for OceanBase Database Oracle Mode are the same as those for OceanBase Database MySQL Mode.
  • The maximum length for a table name in OceanBase Database Oracle Mode is currently 64 characters. This may vary by specific version.

• Temporary tables

  Temporary tables are prefixed with tmp_.

  Additional scenario description

  Resource names should be chosen considering their associated dependencies and to avoid unnecessary ambiguity.

  Notice

  Ambiguity refers to resource names that are in compliance with the naming conventions but have multiple interpretations. For example, a table that starts with tmp_ can be considered a regular table or a temporary table. This situation should be avoided.

Example of error

• Example 1: Does not conform to the OceanBase Database MySQL table naming conventions

  • 1tb: Starts with a number, violating the requirement to start with a lowercase letter.
  • ChineseTable: Contains Chinese characters, violating the requirement to be composed of lowercase letters, numbers, and underscores.
  • tb: Has a length of less than 3, violating the requirement for a length of at least 3 characters.
  • userTable: Contains uppercase letters, violating the requirement to be composed of lowercase letters, numbers, and underscores.
  • user_: Ends with an underscore, violating the requirement to end with a lowercase letter or number.
  • primay: The keyword primay exists in MySQL, violating the requirement to not use keywords or reserved words from OceanBase Database MySQL Mode.

• Example 2: Does not conform to the OceanBase Database Oracle table naming conventions

  • user_table: Contains lowercase letters, violating the requirement to be composed of uppercase letters, numbers, and underscores.

[Mandatory][Style] Index naming conventions

Description

Index naming conventions in databases:

• OceanBase Database in MySQL mode

  • The index name consists of lowercase letters, numbers, and underscores (_). A normal index must start with idx, and a unique index must start with uk.
  • The index name must not exceed the maximum length allowed by the database.
  • The index name must contain meaningful characters. We recommend that you use the column name or an abbreviation of the column name, followed by an underscore (_), to name the index.

  Notice

  The maximum index length allowed by the mainstream versions of MySQL and OceanBase Database in MySQL mode is 64 characters. The maximum index length allowed by different versions may vary.

• Oracle and OceanBase Database in Oracle mode

  • The index name consists of uppercase letters, numbers, and underscores (_). A normal index must start with IDX, and a unique index must start with UK.
  • The index name must not exceed the maximum length allowed by the database.
  • The index name must contain meaningful characters. We recommend that you use the column name or an abbreviation of the column name, followed by an underscore (_), to name the index.

  Notice

  The maximum index length allowed by the mainstream versions of OceanBase Database in Oracle mode is 64 characters. The maximum index length allowed by different versions may vary.

Examples

• Example 1: A normal index in OceanBase Database in MySQL mode does not start with idx

  index email_address(email,address), where the normal index does not start with idx.

• Example 2: A unique index in OceanBase Database in MySQL mode does not start with uk

  unique index idx_email_address(email,address), where the unique index does not start with uk, but with idx, which may cause confusion.

• Example 3: An index in OceanBase Database in MySQL mode is named with characters that have no business meaning

  index idx_1(email,adress), where the index is named with characters that have no business meaning.

• Example 4: The index name is inconsistent in a logical table in OceanBase Database in MySQL mode

  The index name must be consistent. For example, it must be idx_email_address.

  Logical table user_[00-09]
  user_00 index index idx_00_email_address(email,address);
  user_01 index index idx_01_email_address(email,address);
  ...
  user_09 index index idx_09_email_address(email,address);
  

• Example 5: Index name conflicts in the same schema in OceanBase Database in Oracle mode

  10 databases and 100 tables, there are 10 physical tables in the same physical database. For example, in DB00, the indexes named IDX_COLA on the 10 physical tables TB_00, TB_01, ..., TB_09 conflict with each other.

  CREATE TABLE DB[00-09].TB_[00-99](
      COLA NUMBER,
      INDEX IDX_COLA("COLA");
  ); //
  

• Example 6: Index conflicts between different tables in a single database in OceanBase Database in Oracle mode

  In DB, the indexes named IDX_COLA on the two tables TB1 and TB2 conflict with each other.

  CREATE TABLE DB.TB_A(
      COLA NUMBER,
      INDEX IDX_COLA("COLA");
  );
  CREATE TABLE DB.TB_B(
      COLA NUMBER,
      INDEX IDX_COLA("COLA");
  );
  

Correct examples

• Example 1: A normal index in OceanBase Database in MySQL mode

  index idx_email_address(email,address), where the normal index starts with idx and consists of the columns email and address, separated by an underscore (_).

• Example 2: A unique index in OceanBase Database in MySQL mode

  unique index uk_email_address(email,address), where the unique index starts with uk and consists of the columns email and address, separated by an underscore (_).

• Example 3: An index in OceanBase Database in MySQL mode that contains an abbreviated column name

  index idx_email_add(email,address), where the index consists of the columns email and address, and the column address is abbreviated to add.

• Example 6: A single database in OceanBase Database in Oracle mode

  You can name the index with information about the table name to avoid conflicts between different tables:

  CREATE TABLE DB.TBA(
      COLA NUMBER,
      INDEX IDX_TBA_COLA("COLA");
  );  // Index on the COLA column of Table 1
  CREATE TABLE DB.TBB(
      COLA NUMBER,
      INDEX IDX_TBB_COLA("COLA");
  );  // Index on the COLA column of Table 2
  

[MUST][STYLE] Table and column comment

Description

The table comment must be specified when a table is created. It is recommended to specify comments for necessary columns.

Sample error code

CREATE TABLE user_table (
  id int,
  name varchar(255),
  address varchar(255),
  city varchar(255)
)

Sample correct code

CREATE TABLE user_table (
  id int,
  name varchar(255),
  address varchar(255) comment 'Address',
  city varchar(255)
) COMMENT = 'Ticket related person'

[MUST][QUALITY] The auto-increment column type in OceanBase Database must be bigint or bigint unsigned, and int is not allowed

Description

OceanBase Database generates an interval of values for an auto-increment column each time (usually 100w in a production environment). In some scenarios, OceanBase Database may discard the remaining values in the interval and generate new values, causing the auto-increment values to "jump". To avoid the auto-increment values reaching the maximum value allowed by the data type, the auto-increment column type must be bigint or bigint unsigned.

The auto-increment values may jump during their use. The scenarios that trigger a jump include:

• The two accesses are not to the same partition.
• A switchover occurs.
• A major compaction occurs.
• A large auto-increment value is explicitly inserted.
• An insert on duplicate update occurs (this issue has been improved. If the auto-increment column is not explicitly set to a new value in the update, the values will not jump).