Schema specifications

[MUST][Style] Table naming conventions

Note

Naming standards for tables (physical and logical) in the database:

• General specifications for OceanBase Database in MySQL mode

  • Must consist of lowercase letters, digits, and underscores, start with a lowercase letter, and end with a lowercase letter or digit.
  • The length is greater than or equal to 3 (R&D specifications) but less than the maximum number of characters allowed for table names.
  • Do not use the names of the keywords and reserved words in OceanBase Database's MySQL mode.

  Note

  The maximum allowed length of a table name in MySQL mode for OceanBase Database is 64 characters. However, specific versions might have different limits.

• General specifications of OceanBase Database in Oracle mode

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

  Note

  • Oracle Mode in OceanBase Database is similar to MySQL Mode in OceanBase Database except that it distinguishes between uppercase and lowercase letters.
  • The maximum allowed length of a table name in Oracle Mode in OceanBase Database is 64 characters at present. The actual limit may vary with the specific version.

• Temporary Tables

  Temporary tables are prefixed with tmp_.

  Addition scenario description

  Resource names should take into account their limited association dependencies and avoid unnecessary ambiguity.

  Note

  Ambiguity refers to names that although meet the naming conventions, can have multiple interpretations. For example, a table that starts with tmp_ can be a normal table, and is also sometimes a temporary table. Such ambiguity should be avoided.

Error Code Examples

• Error example 1: The table name does not conform to the MySQL table naming conventions in OceanBase Database.

  • 1tb: starts with a digit, violates the requirement to start with a lowercase letter.
  • table name containing Chinese characters: the name contains Chinese characters, which violates the "composed of lowercase letters, numbers, and underscores" rule.
  • tb: The length is less than 3, which violates the "Length greater than or equal to 3 and less than 65 characters" specification.
  • userTable: A table name that contains uppercase letters violates the "composed of lowercase letters, numbers, and underscores" standard.
  • user_: Table names should not end with an underscore, as they are required to end with a lowercase letter or a digit.
  • primay: "primay" is a keyword in MySQL. The naming convention "use keywords or reserved words existing in OceanBase Database MySQL mode" is violated.

• Error example 2: The table name does not conform to the OceanBase Database Oracle mode naming rules.

  • user_table: A table name cannot contain lowercase letters. Table names must consist of uppercase letters, numbers, and underscores.

[Mandatory][Style] Index naming convention

Description

Index naming conventions in the database:

• 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, and separate the two parts with an underscore (_).

  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 may vary with different versions.

• 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, and separate the two parts with an underscore (_).

  Notice

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

Examples

• 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. If it starts with idx, it may cause confusion.

• Example 3: The index name in OceanBase Database in MySQL mode does not contain meaningful characters

  index idx_1(email,adress) indicates that the index name does not contain meaningful characters.

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

  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 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 in the physical tables TB_00, TB_01, ..., TB_09 are conflicting.

  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 in the tables TB1 and TB2 are conflicting.

  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) indicates that the normal index starts with idx and contains the columns email and address, which are separated with an underscore (_).

• 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 an underscore (_).

• 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 name 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");
  );  // 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] Table & column comment

Description

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

Sample code

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

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

Description

OceanBase Database takes an interval (usually 1 million) for an auto-increment column when it is created. In some scenarios, OceanBase Database may discard the remaining values in the interval and take new values, causing the auto-increment values to "jump". To avoid the auto-increment values from reaching the maximum value allowed by the type, the auto-increment column type must be bigint or bigint unsigned.

During the use of an auto-increment column, the auto-increment values may "jump". The scenarios that cause the jump include:

• The two accesses are not on the same partition.
• A switchover occurs.
• A major compaction occurs.
• A large auto-increment value is actively 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 statement, the jump will not occur.)