Schema Specification

[Mandatory][Style] Table naming convention

Note

Physical & logical table naming convention in the database:

• General specifications of OceanBase Database in MySQL mode

  • Must consist of lowercase letters, numbers, and underscores, and must start and end with a lowercase letter or a number.
  • Length greater than or equal to 3 (development specifications) and less than the maximum character limit for database table names.
  • Not named as a keyword or reserved word that exists in OceanBase Database in MySQL Mode.

  Note

  In the MySQL mode of OceanBase Database, the maximum length of a table name is 64, with possible variations depending on the specific version.

• General specifications of OceanBase Database in Oracle mode

  • Consists of uppercase letters, numbers, and underscores, and starts and ends with an uppercase letter or number.
  • The length is greater than or equal to 3 (R&D specifications) and less than the maximum number of characters allowed for a database table name.
  • Not use keywords or reserved words in OceanBase Database in Oracle mode.

  Notice

  • The specifications for OceanBase Database in Oracle mode, except for case sensitivity requirements, are the same as those for OceanBase Database in MySQL mode.
  • The maximum length of a current table name in OceanBase Database in Oracle mode is 64, which may vary with the specific version.

• Temporary Table

  Temporary tables are prefixed with tmp_.

  Additional Scenario Description

  Resource names should be chosen with their associated dependencies in mind, and with care to avoid unnecessary ambiguity.

  Note

  Ambiguity refers to resource names that conform to specifications but have multiple interpretations. For example, a table named with tmp_ could be treated as a regular table, which might also be seen as a temporary table. Such cases should be avoided.

Error Code Examples

• Example 1: Does not comply with the MySQL table name naming conventions of OceanBase Database

  • 1tb: It starts with a digit, which violates the requirement of starting with a lowercase letter.
  • Chinese table name: The name contains a Chinese character, which violates the specification that the name must consist solely of lowercase letters, numbers, and underscores.
  • tb: less than 3, violates the "length greater than or equal to 3 and less than 65 characters" specification.
  • userTable: Table name contains uppercase letters, which violates the "composed of lowercase letters, numbers, and underscores" specification.
  • user_: Table name ends with an underscore, which violates the specification of ending with a lowercase letter or a number.
  • primay: 'primay' is a keyword in MySQL. It violates the specification of "keywords and reserved words existing in OceanBase Database in MySQL mode".

• Error Example 2: the name does not conform to the table naming rules of OceanBase Database's Oracle mode

  • user_table: The table name contains lowercase letters, which violates the specification that it should consist solely of uppercase letters, numbers, and underscores.

[Mandatory][Style] Index naming conventions

Description

Index naming conventions in the database:

• 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.
  • The length of an index name must not exceed the limit of the database.
  • Index names must contain meaningful characters. We recommend that you use the names of the columns or an appropriate abbreviation of the column names, separated by underscores _.

  Notice

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

  Notice

  The maximum index length allowed in the mainstream versions of OceanBase Database in Oracle mode is 64 characters. The actual limit 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): The normal index 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): The unique index does not start with uk. It starts with idx, which can cause confusion.

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

  index idx_1(email,adress): The index name has no business meaning.

• Example 4: The index names are inconsistent in the logical tables of OceanBase Database in MySQL mode

  The index names should be consistent and 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 exist 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 exist 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): The normal index starts with idx and contains 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): The unique index starts with uk and contains the columns email and address, separated by an underscore _.

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

  index idx_email_add(email,address): The index contains the column name address, which is abbreviated to add.

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

  Indexes can be named 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 field of Table 1
  CREATE TABLE DB.TBB(
      COLA NUMBER,
      INDEX IDX_TBB_COLA("COLA");
  );  // Index on the COLA field of Table 2
  

[Mandatory][Style] Table & column comments

Description

The comment for a table must be specified when a table is created (mandatory). 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 people'

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

Description

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

The auto-increment values may "jump" during the process of using the auto-increment column. The scenarios that trigger the jump include:

• 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 (which has been improved. If the auto-increment column is not explicitly set to a new value in the update, the value does not jump).