This article introduces the incompatible scenarios for deleting fields under different constraints in MySQL database and OceanBase database MySQL compatible mode.
Note
In the following tables, an empty cell in the type column indicates that the type is not supported by the corresponding constraint.
Delete a PRIMARY KEY column
The following table describes whether you can delete PRIMARY KEY columns of different types by executing the ALTER TABLE DDL statement in a MySQL database and a MySQL-compatible tenant of OceanBase Database.
| Column type in a MySQL database | Delete allowed? | Column type in a MySQL-compatible tenant of OceanBase Database | Delete allowed? |
|---|---|---|---|
| INT | Yes | INTEGER | No |
| TINYINT | Yes | TINYINT | No |
| SMALLINT | Yes | SMALLINT | No |
| MEDIUMINT | Yes | MEDIUMINT | No |
| BIGINT | Yes | BIGINT | No |
| BOOL | Yes | BOOLEAN | No |
| DECIMAL | Yes | DECIMAL | No |
| NUMERIC | Yes | NUMERIC | No |
| REAL | Yes | FLOAT | No |
| DOUBLE | Yes | DOUBLE | No |
| FLOAT | Yes | FLOAT | No |
| BIT | Yes | BIT | No |
| CHAR | Yes | CHAR | No |
| NCHAR | Yes | CHAR | No |
| VARCHAR | Yes | VARCHAR | No |
| BINARY | Yes | BINARY | No |
| VARBINARY | Yes | VARBINARY | No |
| LONG VARBINARY | Yes | ||
| YEAR | Yes | YEAR | No |
| DATE | Yes | DATE | No |
| TIME | Yes | TIME | No |
| TIMESTAMP | Yes | TIMESTAMP | No |
| DATETIME | Yes | DATETIME | No |
| TINYBLOB | Yes | ||
| MEDIUMBLOB | Yes | ||
| BLOB | Yes | ||
| LONGBLOB | Yes | ||
| TINYTEXT | Yes | ||
| MEDIUMTEXT | Yes | ||
| TEXT | Yes | ||
| LONGTEXT | Yes | ||
| ENUM | Yes | ENUM | No |
| SET | Yes | SET | No |
| JSON | Yes (However, if a column is involved in a functional dependency, the column cannot be deleted.) |
Delete a UNIQUE KEY column
If a UNIQUE KEY column is of a type described in the preceding table, whether you can delete the column by executing the ALTER TABLE DDL statement in a MySQL database and a MySQL-compatible tenant of OceanBase Database is the same as that for a PRIMARY KEY column.
Delete a KEY column
If a KEY column is of a type described in the preceding table, whether you can delete the column by executing the ALTER TABLE DDL statement in a MySQL database and a MySQL-compatible tenant of OceanBase Database is the same as that for a PRIMARY KEY column.
Delete a FULLTEXT-indexed column
The following table describes whether you can delete FULLTEXT indexed columns of different types by executing the ALTER TABLE DDL statement in a MySQL database and a MySQL-compatible tenant of OceanBase Database.
| Column type in a MySQL database | Delete allowed? | Column type in a MySQL-compatible tenant of OceanBase Database | Delete allowed? |
|---|---|---|---|
| CHAR | Yes | CHAR | No |
| VARCHAR | Yes | VARCHAR | No |
| TINYTEXT | Yes | TINYTEXT | No |
| MEDIUMTEXT | Yes | MEDIUMTEXT | No |
| TEXT | Yes | TEXT | No |
| LONGTEXT | Yes | LONGTEXT | No |
| BINARY | No | ||
| VARBINARY | No | ||
| NCHAR | Yes | CHAR | No |
| JSON | Yes (However, if a column is involved in a functional dependency, the column cannot be deleted.) |