Skip to main content

2. Table Definition

Must Use INNODB Storage Engine​

Code: InnodbEngineRequired

Rule Description: This rule requires that all table creation operations must specify INNODB as the storage engine because INNODB provides transaction support, row-level locking, and better recovery capabilities.

Alert Level: Warning

Review Object: CREATE TABLE

Trigger Condition: The storage engine INNODB is not specified when creating a table.

Configurable: No

Supported Database Types: MySQL

Prohibition of Partitioned Tables​

Code: PartitionedTableDisallowed

Rule Description: This rule prohibits the creation of partitioned tables as they increase management complexity and may affect performance in certain cases.

Alert Level: Notice

Review Object: CREATE TABLE

Trigger Condition: The partitioning feature is used when creating a table.

Configurable: No

Supported Database Types: MySQL

Only One Auto-Increment Column Per Table​

Code: OneIdentityColumnAllowed

Rule Description: Requires that each table can only have one auto-increment column to avoid complex index management and potential performance issues.

Alert Level: Warning

Review Object: CREATE TABLE

Trigger Condition: Multiple auto-increment columns are defined in the table.

Configurable: Yes

Example: The expression can be configured as 0.8, indicating that MySQL InnoDB and MyISAM engines do not allow multiple auto-increment fields.

Supported Database Types: All

The Number of Table Columns Should Not Exceed the Threshold​

Code: NumOfColumnsExceedThreshold

Rule Description: It is recommended that the number of columns in a table should not exceed a specific threshold to optimize performance and simplify management.

Alert Level: Warning

Review Object: CREATE TABLE

Trigger Condition: The number of columns in the table exceeds the preset threshold (e.g., 32).

Configurable: Yes

Example: The expression can be configured as 32, indicating that the number of columns in the table should not exceed 32.

Supported Database Types: All

Required Columns and Types in a Table​

Code: SpecificColumnsRequired

Rule Description: Requires that tables must contain specific columns and their data types, such as id and timestamp columns, to meet the needs of the application.

Alert Level: Warning

Review Object: CREATE TABLE

Trigger Condition: The table does not contain the specified columns and their types.

Configurable: Yes

Example: The expression can be configured as id:unsigned int,created_ts:timestamp,updated_ts:timestamp, indicating that the table must contain these columns and their types.

Supported Database Types: All

Prohibition of Using Views​

Code: ViewDisallowed

Rule Description: Prohibits the creation of views as they increase query complexity and may affect performance.

Alert Level: Warning

Review Object: CREATE VIEW

Trigger Condition: A view is created.

Configurable: No

Supported Database Types: MySQL

Limit on the Number of Indexes per Table​

Code: NumOfIndexesExceedThreshold

Rule Description: Limits the number of indexes per table to avoid performance degradation and complex index maintenance.

Alert Level: Warning

Review Object: CREATE INDEX, ALTER TABLE ADD INDEX

Trigger Condition: The number of indexes in the table exceeds the preset threshold (e.g., 5).

Configurable: Yes

Example: The expression can be configured as 5, indicating that the number of indexes in the table should not exceed 5.

Supported Database Types: All