Schema specifications

[Mandatory][Style] Table naming conventions

Description

Table naming conventions in databases (both physical and logical):

• General conventions for OceanBase Database in MySQL mode

  • 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 must not exceed the maximum allowed character limit for database table names.
  • Must not use keywords or reserved words in OceanBase Database in MySQL mode.

  Notice

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

• General conventions for OceanBase Database in Oracle mode

  • 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 must not exceed the maximum allowed character limit for database table names.
  • Must not use keywords or reserved words in OceanBase Database in Oracle mode.

  Notice

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

• Temporary tables

  Temporary tables must start with the tmp_ prefix.

  Additional scenario description

  Resource naming should consider the limitations on associated dependencies and avoid unnecessary ambiguity.

  Notice

  Ambiguity refers to situations where resource names, although compliant with the naming conventions, can be interpreted in multiple ways. For example, a table starting with tmp_ can be considered both a regular table and a temporary table. Such situations should be avoided.

Example of error

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

  • 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 less than 3, violating the requirement for a length of at least 3 and up to 65 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: primay is a keyword in MySQL, violating the requirement to not use keywords or reserved words from OceanBase Database in MySQL mode.

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

  • 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

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

  Notice

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

• Oracle and OceanBase Database in Oracle mode

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

  Notice

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

Examples of error

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

  index email_address(email,address) specifies a normal index that does not start with idx.

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

  unique index idx_email_address(email,address) specifies a unique index that does not start with uk. If it starts with idx, it may cause confusion.

• Example 3: An index name has no business meaning in OceanBase Database in MySQL mode

  index idx_1(email,adress) specifies an index that has no business meaning.

• Example 4: Index names are inconsistent in a logical table in OceanBase Database in MySQL mode

  The index names should be consistent, all 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 of the physical tables TB_00, TB_01...TB_09 conflict.

  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 of tables TB1 and TB2 conflict.

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

Correct code examples

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

  index idx_email_address(email,address) specifies a normal index that starts with idx. The index contains the email and address columns, separated by an underscore (_).

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

  unique index uk_email_address(email,address) specifies a unique index that starts with uk. The index contains the email and address columns, separated by an underscore (_).

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

  index idx_email_add(email,address) specifies an index that contains the email and address columns. The address column is abbreviated to add.

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

  You can add table name information to the index 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
  

[Mandatory][Style] Comment for tables and columns

Description

The comment for a table 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 = 'Person related to tickets'

[Mandatory][Quality] The auto-increment column type must be bigint or bigint unsigned. Using int is not allowed.

Description

OceanBase Database generates an interval of values for an auto-increment column each time (usually 1 million values in a production environment). In some scenarios, OceanBase Database may discard the remaining values in the interval and generate a new one, 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.

During the use of an auto-increment column, you can observe "jumps" in values. The scenarios that cause such jumps include:

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