Change the type of a constrained column
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.
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 | - |