Schema specifications

[Mandatory][Style] Table naming conventions

Remarks

Naming conventions (physical & logical tables) in the database:

• General Specifications of OceanBase Database in MySQL Mode

  • Consists of lowercase letters, numbers, and underscores, and starts and ends with a lowercase letter or a number.
  • The length is greater than or equal to 3 (based on R&D standards) but less than the maximum number of characters allowed for database table names.
  • Do not use keywords or reserved words in MySQL Mode of OceanBase Database.

  Note

  In MySQL mode, the maximum length of the current table name in OceanBase Database is 64 characters. The specific version may have different limits.

• General specifications for Oracle mode of OceanBase Database

  • Consists of uppercase letters, numbers, and underscores, and must start and end with an uppercase letter or number.
  • Length >=3 (R&D specifications) and < the maximum limit of characters in the database table name.
  • Do not use keywords or reserved words in OceanBase Database's Oracle mode.

  Note

  • Other than the case sensitivity requirements, the OceanBase Database Oracle mode specifications are the same as the OceanBase Database MySQL mode specifications.
  • The maximum length of a table name in the OceanBase Database Oracle mode is 64 characters. Different limits may be in place depending on the specific version.

• Temporary Table

  Temporary tables are prefixed with tmp_.

  Additional Scenario Description

  Resource names should be chosen with consideration to their limited association dependencies and the avoidance of unnecessary ambiguity.

  Note

  Ambiguity refers to a situation where, even though the naming convention is followed, the name can be interpreted in multiple ways. For example, a table named with tmp_ as a prefix is considered a regular table, but could also be interpreted as a temporary table. This ambiguity should be avoided.

Example of an error code

• Error Example 1: Does not conform to the naming rules of MySQL table names in OceanBase Database

  • 1tb: Starts with a number, which violates the requirement that it should start with a lowercase letter.
  • table name contains Chinese characters: The table name contains a Chinese character, which violates the requirement that it consist of lowercase letters, numbers, and underscores.
  • tb: The value is shorter than 3, violating the specification of "length greater than or equal to 3 and less than 65".
  • userTable: the name contains uppercase letters. The naming convention for table names requires that the name consist solely of lowercase letters, numbers, and underscores.
  • user_: The table name ends with an underscore, violating the "ends with a lowercase letter or number" rule.
  • primay: 'primay' is a MySQL keyword, which violates the naming convention of "using keywords and reserved words from OceanBase Database in MySQL mode".

• Sample error 2: The table name does not conform to the Oracle mode of OceanBase Database.

  • user_table: the table name contains lowercase letters, which violates the "composed of uppercase letters, numbers, and underscores" specification.

[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 name starts with idx, and a unique index name starts with uk.
  • The length of an index name does not exceed the limit of the database.
  • Index names contain meaningful characters. We recommend that you use the names of the columns that the index contains or an appropriate abbreviation of the column names, and separate the names with underscores (_).

  Notice

  The index name length limit is 64 characters for the mainstream versions of MySQL and OceanBase Database in MySQL mode. The limit may vary in different versions.

• Oracle and OceanBase Database in Oracle mode

  • Index names consist of uppercase letters, numbers, and underscores (_). A normal index name starts with IDX, and a unique index name starts with UK.
  • The length of an index name does not exceed the limit of the database.
  • Index names contain meaningful characters. We recommend that you use the names of the columns that the index contains or an appropriate abbreviation of the column names, and separate the names with underscores (_).

  Notice

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

Examples of error

• Example 1: The name of a normal index does not start with idx in OceanBase Database in MySQL mode

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

• Example 2: The name of a unique index does not start with uk in OceanBase Database in MySQL mode

  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: The name of an index contains meaningless characters in OceanBase Database in MySQL mode

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

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

  The names of indexes in a logical table should be consistent. They should 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

  10 databases and 100 tables, in the same physical database, there are 10 physical tables of a logical table. 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 indexes named IDX_COLA of the 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 contains the columns email and address, which are separated with 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 contains the columns email and address, which are separated with underscores (_).

• 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) indicates that the index contains the column address, which is abbreviated to add.

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

  Indexes can contain information about the table name to avoid conflicts between different tables:

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

[MUST][STYLE] Comment on table and columns

Description

The comment on the table is required when you create a table. It is recommended to add comments to 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 a ticket'

[MUST][QUALITY] The auto-increment column must be of the BIGINT or BIGINT UNSIGNED type

Description

OceanBase Database generates an interval of values (usually 100w values) for an auto-increment column each time. In some scenarios, OceanBase Database may discard the remaining values in the interval and generate new values. This results in "jumping" values in the auto-increment column. To avoid the auto-increment values reaching the maximum value allowed by the data type, the BIGINT or BIGINT UNSIGNED type is enforced.

During the use of an auto-increment column, "jumping" may be observed. The following scenarios may trigger this issue:

• 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 operation is performed on duplicate entries (the issue is improved. If the auto-increment column is not explicitly set to the new value in the update statement, the value does not jump.)