Schema specifications

[Mandatory] [Style] Table naming specifications

Description

Specifications for naming tables (physical & logical) in databases are as follows:

• Table naming specifications for the MySQL-compatible mode of OceanBase Database

  • A table name can contain lowercase letters, digits, and underscores (_). It must start with a lowercase letter and end with a lowercase letter or digit.
  • A table name can contain at least three characters (based on R&D specifications) and no more than the maximum characters allowed.
  • A table name cannot contain keywords or reserved words of OceanBase Database's MySQL-compatible mode.

  Notice

  The MySQL-compatible mode of OceanBase Database supports a maximum length of 64 characters for a table name, which may vary with the version.

• Table naming specifications for OceanBase Database's Oracle-compatible mode

  • A table name can contain uppercase letters, digits, and underscores (_). It must start with an uppercase letter and end with an uppercase letter or digit.
  • A table name can contain at least three characters (based on R&D specifications) and no more than the maximum characters allowed.
  • A table name cannot contain keywords or reserved words of OceanBase Database's Oracle-compatible mode.

  Notice

  • The table naming specifications for OceanBase Database's Oracle-compatible mode are the same as those for OceanBase Database's MySQL-compatible mode except for the letter case requirements.
  • OceanBase Database's Oracle-compatible mode supports a maximum length of 64 characters for a table name, which may vary with the version.

• Naming specifications for temporary tables

  The name of a temporary table contains a tmp_ prefix.

  Considerations

  Resource naming is also constrained by associated operations in database design, and needs to avoid unnecessary ambiguity.

  Notice

  An ambiguous resource name, although conforming to specifications, can be interpreted differently. For example, a normal table with a name starting with tmp_ can be mistaken for a temporary table.

Examples of incorrect code

• Example 1: Violation to naming specifications for OceanBase Database's MySQL-compatible mode

  • 1tb: The table name starts with a digit, but a table name must start with a lowercase letter.
  • ChineseTable: The table name contains Chinese characters, but a table name can contain only lowercase letters, digits, and underscores (_).
  • tb: The table name contains less than 3 characters, but a table name must contain 3 to 65 characters.
  • userTable: The table name contains an uppercase letter, but a table name can contain only lowercase letters, digits, and underscores (_).
  • user_: The table name ends with an underscore(_), but a table must end with a lowercase letter or digit.
  • primary: The table name is a keyword in the MySQL-compatible mode of OceanBase Database, but a table name cannot contain keywords or reserved words.

• Example 2: Violation to naming specifications for OceanBase Database's Oracle-compatible mode

  • user_table: The table name contains a lowercase letter, but a table name can contain only uppercase letters, digits, and underscores (_).

[Mandatory] [Style] Index naming specifications

Description

Specifications for naming indexes in databases are as follows:

• Index naming specifications for OceanBase Database's MySQL-compatible mode

  • An index name can contain lowercase letters, digits, and underscores (_). The name of a common index must start with idx, and the name of a unique index must start with uk.
  • An index name cannot exceed the maximum length allowed by the database.
  • An index name must contain characters that describe the business meaning of the index. We recommend that you include the full or abbreviated column names in the index name and separate the column names with underscores (_).

  Notice

  In mainstream versions of MySQL and OceanBase Database's MySQL-compatible mode, an index name can contain up to 64 characters, which may vary with the version.

• Index naming specifications for Oracle Database and OceanBase Database's Oracle-compatible mode

  • An index name can contain uppercase letters, digits, and underscores (_). The name of a common index must start with IDX, and the name of a unique index must start with UK.
  • An index name cannot exceed the maximum length allowed by the database.
  • An index name must contain characters that describe the business meaning of the index. We recommend that you include the full or abbreviated column names in the index name and separate the column names with underscores (_).

  Notice

  The current mainstream version of OceanBase Database in Oracle-compatible mode limits index length to 64 characters, though this restriction may vary across different versions.

Examples of incorrect code

• Example 1: The name of a common index does not start with idx in OceanBase Database's MySQL-compatible mode

  index email_address(email,address) is a common index but its name does not start with idx.

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

  unique index idx_email_address(email,address) is a unique index but its name starts with idx instead of uk, which may cause ambiguity.

• Example 3: The name of an index contains meaningless characters in OceanBase Database's MySQL-compatible mode

  The name of index idx_1(email,adress) contains meaningless characters.

• Example 4: The index names are inconsistent for physical tables of the same logical table in OceanBase Database's MySQL-compatible mode

  The name of all indexes in the following example must be idx_email_address.

  Physical tables of the same 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: The index names conflict with each other in the same schema in OceanBase Database's Oracle-compatible mode

  In the following example of 10 databases × 100 tables per database, each physical database contains 100 logical tables of the same logical table. For example, in database DB00, TB_00, TB_01...TB_99 are physical tables of the same logical table but they have the same index name IDX_COLA. This causes a naming conflict.

  CREATE TABLE DB[00-09].TB_[00-99](
      COLA NUMBER,
      INDEX IDX_COLA("COLA");
  ); //
  

• Example 6: The index names for different tables in a single database conflict with each other in OceanBase Database's Oracle-compatible mode

  In the database DB, the index name is IDX_COLA for both the tables TB_A and TB_B.

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

Examples of correct code

• Example 1: Common index in OceanBase Database's MySQL-compatible mode

  index idx_email_address(email,address) is a common index whose name starts with idx and contains the indexed columns email and address, which are separated by an underscore (_).

• Example 2: Unique index in OceanBase Database's MySQL-compatible mode

  unique index uk_email_address(email,address) is a unique index whose name starts with uk and contains the indexed columns email and address, which are separated by an underscore (_).

• Example 3: Column abbreviation contained in an index name in OceanBase Database's MySQL-compatible mode

  index idx_email_add(email,address) is an index whose name contains the abbreviation add of the address column.

• Example 4: Index names for different tables in a single database in OceanBase Database's Oracle-compatible mode

  Each index name can contain a table name to avoid conflicts with others.

  CREATE TABLE DB.TBA(
      COLA NUMBER,
      INDEX IDX_TBA_COLA("COLA");
  ); // The index on the COLA column in table TBA
  CREATE TABLE DB.TBB(
      COLA NUMBER,
      INDEX IDX_TBB_COLA("COLA");
  );  // The index on the COLA column in table TBB
  

[Mandatory] [Style] Table and column comments

Description

Enter comments when you create a table. Necessary column comments are also recommended.

Example of incorrect code

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

Example of correct code

CREATE TABLE user_clicks(
  id int,
  name varchar(255),
  address varchar(255) comment 'Address',
  city varchar(255)
) COMMENT = 'Ticket personnel'

[Mandatory] [Quality] Use bigint or bigint unsigned instead of int as the data type of auto-increment columns in OceanBase Database

Description

Each auto-increment column in OceanBase Database has a value range, which is usually [1,1000000] in a production environment. In some scenarios, an auto-increment column hops the values in a section of the range and starts from a value in the next section. To avoid exhaust auto-increment values, that is, reach the maximum value allowed for the data type of a column, you must use bigint or bigint unsigned as the data type.

The following scenarios trigger a value hop in an auto-increment column:

• Accesses to different partitions
• Leader switch
• Major compaction
• Manual insert of a large auto-increment value
• INSERT ON DUPLICATE UPDATE (If you do not explicitly specify auto-increment column = new value after UPDATE, there will not be a value hop.)