Column type change rules

When you change the data type of a column in the Oracle mode of OceanBase Database, you must first follow the table-level column type change rules, especially in scenarios where some data types are forbidden, and then follow the change rules of the source data type and target data type.

Table-level column type change rules

Prohibitory rules

• FOREIGN KEY constraints

  For both online and offline DDL operations: You can only increase or decrease the precision of data types VARCHAR, VARCHAR2, and NVARCHAR2.

• Trigger constraints

  For only offline DDL operations: You cannot change the data type of a column with any triggers not in the DISABLE state. If all triggers are in the DISABLE state, DDL operations are not affected and the new table must contain the triggers in the DISABLE state.

• Partitioning keys

  For both online and offline DDL operations: If a column is a part of the partitioning key, you cannot change the data type or length of this column.

• For both online and offline DDL operations: If a column is referenced by a generated column, you cannot change the data type of this column.

Change rules

• You can change the length of a column without changing its data type. For example, you can change a CHAR column to a CHAR(11) column. Since OceanBase Database allows you to perform online DDL operations to store CHAR data without rewriting data, if the column is referenced by an index table, you can change the length of this column by performing an online DDL operation. If the column is used as a primary key or partitioning key, you need to perform offline DDL operations to change its length. If the column has a CHECK constraint, the column may violate the CHECK constraint after its length is changed and therefore you also need to perform offline DDL operations to change its length. In other words, perform offline DDL operations to change the length of a column without changing its data type when the column has a CHECK constraint, or when it is used as a primary key or partitioning key.

  You can perform online DDL operations to change the length of a VARCHAR column. Because when you perform operations such as specifying LENGTH(c1), the column length remains unchanged. However, you must also change the schema of objects, such as index tables, that depend on this column. In this case, even if the column is used as a primary key or partitioning key, you can still perform online DDL operations to change the column length. In other words, the schemas of objects that depend on this column need to be changed even though the expected data does not change.

• In OceanBase Database, the type change of a column may occur within a category of data types (such as numeric, character, datetime and interval, and rowid data types). If this column is not referenced by an index table, foreign key, CHECK constraint, or primary key, you can perform online DDL operations to change the column type. Otherwise, you need to perform offline DDL operations to change the column type. In other words, offline DDL operations are required for column type change if any objects depend on the column.

• To convert a SIGNED numeric column to an UNSIGNED numeric column (or vice versa), you need to perform offline DDL operations if the change is allowed.

Column-level change rules

In OceanBase Database, all column data types are classified into the following categories: numeric, character, datetime, interval, and rowid data types.

In the current version of OceanBase Database, both the ROWID and UROWID types are implemented as the UROWID type inside the database. Therefore, the conversion rules between UROWID and UROWID also apply between ROWID and UROWID.

In the Oracle mode of OceanBase Database, conversions among data types of different categories are not allowed. The following sections describe the conversion between data types of the same category.

Conversion between numeric data types

Numeric data types include the following ones:

• NUMBER

• FLOAT

  Notice

  FLOAT(p) is not a floating-point data type. It is a subtype of the NUMBER data type. Its binary precision range is [1, 126].

  • BINARY_FLOAT
  • BINARY_DOUBLE

The following table describes the conversion between numeric data types.

Conversion between character data types

Character data types include the following ones:

• CHAR

• VARCHAR and VARCHAR2: VARCHAR and VARCHAR2 are the same data type.

• NCHAR and NVARCHAR2: All characters are represented in two bytes.

• BLOB: The character set is BINARY.

• CLOB: The character set is UTF8MB4.

• RAW: RAW data is stored in binary format regardless of the character set.

The following table describes the conversion between character data types.

Conversion between datetime data types

Datetime data types include the following ones:

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

The following table describes the conversion between datetime data types.

Conversion between interval data types

Interval data types include the following ones:

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

The following table describes the conversion between interval data types.

Conversion between rowid data types

Rowid data types include the following ones:

• ROWID

• UROWID

The following table describes the conversion between rowid data types.