All Products
Search
Document Center

ApsaraDB for OceanBase:Change the type of a constrained column

Last Updated:Mar 17, 2023

This topic describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the data types of columns that have different constraints.

Note
  • In the following tables, the supported target types of columns in Oracle tenants of OceanBase Database are results of type mapping.

  • In the following tables, a hyphen (-) indicates none and 'All' indicates all types.

Change the type of a PRIMARY KEY column

The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a PRIMARY KEY column by using the ALTER TABLE table_name MODIFY DDL statement.

Column type in Oracle database

Supported target type

Column type in Oracle tenant of OceanBase Database

Supported target type

INTEGER

All

NUMBER(38,0)

-

INT

All

NUMBER(38,0)

-

SMALLINT

All

NUMBER(38,0)

-

BINARY_FLOAT

All

BINARY_FLOAT

-

BINARY_DOUBLE

All

BINARY_DOUBLE

-

DECIMAL

All

NUMBER

-

DEC

All

NUMBER

-

NUMERIC

All

NUMBER

-

REAL

All

NUMBER

-

FLOAT

All

FLOAT

-

NUMBER

All

NUMBER

-

NCHAR

All

NCHAR

-

CHAR

All

CHAR

-

CHARACTER

All

CHAR(n)

-

NVARCHAR2

All

NVARCHAR2(size)

-

VARCHAR2

All

VARCHAR2

-

VARCHAR

All

VARCHAR2

-

RAW

All

RAW

-

ROWID

All

ROWID

UROWID

UROWID

All

UROWID

ROWID

TIMESTAMP

All

TIMESTAMP

-

DATE

All

DATE

-

INTERVAL YEAR TO MONTH

All

INTERVAL YEAR TO MONTH

-

INTERVAL DAY TO SECOND

All

INTERVAL DAY TO SECOND

-

Change the type of a UNIQUE KEY column

The incompatibilities between the Oracle database and the Oracle tenant of OceanBase Database in changing the type of a UNIQUE KEY column by using the ALTER TABLE table_name MODIFY DDL statement are the same as that in changing the type of a PRIMARY KEY column.

Change the type of a column that has a CHECK constraint

The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a column that has a CHECK constraint by using the ALTER TABLE table_name MODIFY DDL statement.

Column type in Oracle database

Supported target type

Column type in Oracle tenant of OceanBase Database

Supported target type

INTEGER

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INT\SMALLINT

NUMBER(38,0)

-

INT

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\SMALLINT

NUMBER(38,0)

-

SMALLINT

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\INT

NUMBER(38,0)

-

BINARY_FLOAT

-

BINARY_FLOAT

-

BINARY_DOUBLE

-

BINARY_DOUBLE

-

DECIMAL

NUMERIC\NUMBER\DEC\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

DEC

NUMERIC\NUMBER\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

NUMERIC

NUMBER\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

REAL

NUMBER\DEC\DECIMAL\FLOAT\INT\SMALLINT\INTEGER

NUMBER

-

FLOAT

NUMERIC\NUMBER\DEC\DECIMAL\REAL\INT\SMALLINT\INTEGER

FLOAT

-

NUMBER

NUMERIC\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

NCHAR

CHAR\CHARACTER\NVARCHAR2\VARCHAR2\VARCHAR

NCHAR

-

CHAR

NCHAR\CHARACTER\NVARCHAR2\VARCHAR2\VARCHAR

CHAR

-

CHARACTER

NCHAR\CHAR\NVARCHAR2\VARCHAR2\VARCHAR

CHAR

-

NVARCHAR2

NCHAR\CHAR\CHARACTER\VARCHAR2\VARCHAR

NVARCHAR2

-

VARCHAR2

NCHAR\CHAR\CHARACTER\NVARCHAR2\VARCHAR

VARCHAR2

-

VARCHAR

NCHAR\CHAR\CHARACTER\NVARCHAR2\VARCHAR2

VARCHAR2

-

RAW

-

RAW

-

ROWID

-

ROWID

UROWID

UROWID

-

UROWID

ROWID

TIMESTAMP

DATE

TIMESTAMP

-

TIMESTAMP WITH LOCAL TIME ZONE

-

TIMESTAMP WITH LOCAL TIME ZONE

-

DATE

TIMESTAMP

DATE

-

INTERVAL YEAR TO MONTH

-

INTERVAL YEAR TO MONTH

-

INTERVAL DAY TO SECOND

-

INTERVAL DAY TO SECOND

-

Change the type of a FOREIGN KEY column

The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a FOREIGN KEY column by using the ALTER TABLE table_name MODIFY DDL statement.

Column type in Oracle database

Supported target type

Column type in Oracle tenant of OceanBase Database

Supported target type

INTEGER

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INT\SMALLINT

NUMBER(38,0)

-

INT

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\SMALLINT

NUMBER(38,0)

-

SMALLINT

NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\INT

NUMBER(38,0)

-

BINARY_FLOAT

-

BINARY_FLOAT

-

BINARY_DOUBLE

-

BINARY_DOUBLE

-

DECIMAL

NUMERIC\NUMBER\DEC\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

DEC

NUMERIC\NUMBER\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

NUMERIC

NUMBER\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

REAL

NUMBER\DEC\DECIMAL\FLOAT\INT\SMALLINT\INTEGER

NUMBER

-

FLOAT

NUMERIC\NUMBER\DEC\DECIMAL\REAL\INT\SMALLINT\INTEGER

FLOAT

-

NUMBER

NUMERIC\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER

NUMBER

-

NCHAR

CHAR\CHARACTER

NCHAR

-

CHAR

NCHAR\CHARACTER

CHAR

-

CHARACTER

NCHAR\CHAR

CHAR

-

NVARCHAR2

VARCHAR2\VARCHAR

NVARCHAR2

-

VARCHAR2

NVARCHAR2\VARCHAR

VARCHAR2

-

VARCHAR

NVARCHAR2\VARCHAR2

VARCHAR2

-

RAW

-

RAW

-

ROWID

-

ROWID

UROWID

UROWID

-

UROWID

ROWID

TIMESTAMP

-

TIMESTAMP

-

TIMESTAMP WITH LOCAL TIME ZONE

-

TIMESTAMP WITH LOCAL TIME ZONE

-

DATE

-

DATE

-

INTERVAL YEAR TO MONTH

-

INTERVAL YEAR TO MONTH

-

INTERVAL DAY TO SECOND

-

INTERVAL DAY TO SECOND

-