Schema Specification

[Mandatory][Style] Table naming conventions

Description

Table naming conventions in databases (physical and logical):

• General OceanBase Database MySQL Mode conventions

  • Composed of lowercase letters, numbers, and underscores, starting with a lowercase letter and ending with a lowercase letter or number.
  • Length is greater than or equal to 3 (development specifications) and less than the maximum allowed character limit for database table names.
  • Does not use keywords or reserved words in OceanBase Database MySQL Mode for naming.

  Notice

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

• General OceanBase Database Oracle Mode conventions

  • Composed of uppercase letters, numbers, and underscores, starting with an uppercase letter and ending with an uppercase letter or number.
  • Length is greater than or equal to 3 (development specifications) and less than the maximum allowed character limit for database table names.
  • Does not use keywords or reserved words in OceanBase Database Oracle Mode for naming.

  Notice

  • Except for the case sensitivity requirement for letters, the 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 depending on the specific version.

• Temporary tables

  Temporary tables are prefixed with tmp_.

  Additional scenario description

  Considerations for resource naming should include limitations on associated dependencies and the avoidance of unnecessary ambiguity.

  Notice

  An ambiguous name is one that, while conforming to the naming conventions, has multiple interpretations. For example, a table that starts with tmp_ can be a regular table or a temporary table. This situation should be avoided.

Examples of error codes

• Example 1: Does not conform to the table naming conventions of OceanBase Database 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 that the length be greater than or equal to 3 and less than 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 not to use keywords or reserved words from OceanBase Database MySQL Mode.

• Example 2: Does not conform to the table naming conventions of OceanBase Database 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 length limit of the database.
  • Index names must contain meaningful characters. We recommend that you use the column name or an abbreviation of the column name, separated by underscores _.

  Notice

  The index length limit is 64 characters for the mainstream versions of MySQL and OceanBase Database in MySQL mode. The limit 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 length limit of the database.
  • Index names must contain meaningful characters. We recommend that you use the column name or an abbreviation of the column name, separated by underscores _.

  Notice

  The index length limit is 64 characters for the mainstream versions of OceanBase Database in Oracle mode. The limit may vary with the versions.

Examples of error

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

  index email_address(email,address) indicates that 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) indicates that the unique index does not start with uk. It starts with idx, which may cause confusion.

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

  index idx_1(email,adress) indicates that the index name has no business meaning.

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

  The index names must be consistent. They must all 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

  Ten 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 are the same.

  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 index IDX_COLA of tables TB1 and TB2 are the same.

  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) indicates that the normal index starts with idx, and the columns in the index email and address are separated by underscores _.

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

  unique index uk_email_address(email,address) indicates that the unique index starts with uk, and the columns in the index email and address are separated by underscores _.

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

  index idx_email_add(email,address) indicates that the column address is abbreviated to add.

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

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

  CREATE TABLE DB.TBA(
      COLA NUMBER,
      INDEX IDX_TBA_COLA("COLA");
  );  // The index of the COLA field in table 1
  CREATE TABLE DB.TBB(
      COLA NUMBER,
      INDEX IDX_TBB_COLA("COLA");
  );  // The index of the COLA field in table 2
  

[MUST][STYLE] Table & column comment

Description

The comment of the table must be specified when the table is created. It is recommended to specify the comments of 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 people'

[MUST][QUALITY] The auto-increment column type must be bigint or bigint unsigned. Using int is prohibited.

Description

OceanBase Database generates an interval of auto-increment values (usually 1 million values) for each auto-increment column. In some scenarios, OceanBase Database may discard the remaining values of the interval and generate a new one, causing the auto-increment values to "skip" (jump). To avoid the auto-increment values reaching the maximum value allowed by the type, we recommend that you use the bigint or bigint unsigned type.

During the use of auto-increment columns, you may observe value "skips". The following scenarios may trigger a value skip:

• The two accesses are not on 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 is performed. If the auto-increment column is not explicitly set to the new value in the update statement, no value skip is triggered.