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 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 the MySQL mode of OceanBase Database.

  Notice

  The MySQL 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 the Oracle mode of OceanBase Database

  • 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 in the Oracle mode of OceanBase Database.

  Notice

  • The table naming specifications for the Oracle mode of OceanBase Database are the same as those for the MySQL mode of OceanBase Database except for the letter case requirements.
  • The Oracle mode of OceanBase Database 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 the MySQL mode of OceanBase Database

  • 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 mode of OceanBase Database, but a table name cannot contain keywords or reserved words.

• Example 2: Violation to naming specifications for the Oracle mode of OceanBase Database

  • 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 the MySQL mode of OceanBase Database

  • 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 (_).
  • In sharding scenarios, the index names for all physical tables of the same logical table must be the same and not distinguished by the physical table key.

  Notice

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

• Index naming specifications for Oracle Database and the Oracle mode of OceanBase Database

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

  Index names must be unique throughout a schema. In sharding scenarios, the index names for physical tables of the same logical table can be the same, or distinguished by the physical table key or table name. However, the index names cannot include both the table key and table name.

  Notice

  • In mainstream versions of Oracle Database and the Oracle mode of OceanBase Database, an index name can contain up to 64 characters, which may vary with the version.
  • In sharding scenarios, the index names for physical tables of the same logical table can be the same only when the number of databases is the same as that of tables in each database, for example, 10 databases × 10 tables per database or 100 databases × 100 tables per database. In other cases, such as 10 databases × 100 tables per database, same index names will cause conflicts.
  • In sharding scenarios, to distinguish index names for different physical tables of the same logical table by the physical table key, you can include a physical table key, such as 00-09 or 000-099, in each index name. This avoids index naming conflicts in the same schema.
  • In sharding scenarios, to distinguish index names for different physical tables of the same logical table by the physical table name, you can include a physical table name, such as TB_00-TB_09 or TB_000-TB_099, in each index name. Since physical table names contain table keys, this method also avoids index naming conflicts in the same schema.
  • In sharding scenarios, if different physical tables of the same logical table have different index names, the use of hints may be restricted. This is because the database cannot parse the physical table key in the name of an index specified in a hint. If indexes must be used in hints, we recommend that you use a logical database where the number of databases is the same as that of tables in each database.

Examples of incorrect code

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

  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 the MySQL mode of OceanBase Database.

  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 the MySQL mode of OceanBase Database.

  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 the MySQL mode of OceanBase Database.

  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 the Oracle mode of OceanBase Database.

  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 the Oracle mode of OceanBase Database.

  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 the MySQL mode of OceanBase Database.

  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 the MySQL mode of OceanBase Database.

  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 the MySQL mode of OceanBase Database.

  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 physical tables of the same logical table in the Oracle mode of OceanBase Database when the number of databases is the same as that of tables in each database in sharding scenarios.

  -- Index names distinguished by the physical table name
  -- Index names: IDX_TB_00_COLA, IDX_TB_01_COLA...IDX_TB_99_COLA
  CREATE TABLE DB[00-99].TB_[00-99](
      COLA NUMBER,
      INDEX IDX_TB_[00-99]_COLA("COLA");
  );
  
  -- Index names distinguished by the physical table key
  -- Index names: IDX_00_COLA, IDX_01_COLA...IDX_99_COLA
  CREATE TABLE DB[00-99].TB_[00-99](
      COLA NUMBER,
      INDEX IDX_[00-99]_COLA("COLA");
  );
  
  -- Same index names for different physical tables
  -- Note that the number of databases cannot be different from that of tables in each database, for example, 10 databases × 100 tables per database.
  -- Otherwise, index naming conflicts occur in the schema.
  CREATE TABLE DB[00-99].TB_[00-99](
      COLA NUMBER,
      INDEX IDX_COLA("COLA");
  );
  

• Example 5: Index names for different physical tables of the same logical table in the Oracle mode of OceanBase Database when the number of databases is different from that of tables in each database in sharding scenarios.

  Each index name must contain a physical table key to avoid conflicts with other index names for the same logical table. Note that in this case, if an SQL statement contains an index-related hint, the hint becomes invalid.

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

• Example 6: Index names for different tables in a single database in the Oracle mode of OceanBase Database.

  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.

Examples of incorrect code

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

Examples 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 a new or modified asynchronous index only after it takes effect in OceanBase Database

Description

For indexes that are created or modified asynchronously in OceanBase Database, a normal index takes effect on the second day after the operation, while a unique index takes effect on the third day after the operation. Therefore, indexes do not take effect immediately after the schema design ticket is approved and executed. If an index is used during this period, a failure may occur.

Examples of incorrect code

Modify the idx_a index to an index that contains columns a and b. If you drop the original index and create a new one, the new index takes effect on the second day. Before that, no index will be available, which will cause queries using the original idx_a index to become slow queries.

-- Table definition
CREATE TABLE test_index(
  id int primary key,
  a int,
  b int,
  index idx_a(a)
);
-- DDL statements to execute
ALTER TABLE test_index drop index idx_a;
ALTER TABLE test_index add index idx_a_b(a,b);

Examples of correct code

Drop the original index after the new index takes effect.

-- Table definition
CREATE TABLE test_index(
  id int primary key,
  a int,
  b int,
  index idx_a(a)
);

-- DDL statement to execute at T1 point in time for index creation
ALTER TABLE test_index add index idx_a_b(a,b);
-- DDL statement to execute at T2 point in time for dropping the original index after the new one takes effect
ALTER TABLE test_index drop index idx_a;

[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
• Primary/Standby database switchover
• 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.)