Limitations on writing SQL statements

This topic describes the limitations when you write SQL statements in OceanBase Database.

• Limitations on tables with the primary keys are as follows:

  • Each table can have at most one primary key column group.

  • The primary key cannot contain a NULL or empty value.

  • The values of the primary key cannot be modified.

  • A primary key can contain at most 64 columns.

  • The total length of primary key data cannot exceed 16 KB.

• When you create a VARCHAR or VARCHAR2 column, you must specify the maximum length for the column, which must be at least 1 byte.

• NUMBER indicates a variable-length and precise numeric value type and occupies a space of 4 to 40 bytes. A NUMBER column can store zeros, floating-point numbers, and positive and negative fixed-point numbers. The NUMBER data type features higher data precision, versatility, and portability. However, it has a lower operational efficiency in comparison with the FLOAT data type.

• Reference an attribute or method of an object type:

  • To reference an attribute or method of an object type in an SQL statement, use a table alias to fully qualify the reference.

  • To reference a member method that does not accept parameters, you must provide empty parentheses.

• When you use partition extensions and subpartition extensions, note the following limitations:

  • If a partition extension or subpartition extension does not contain a remote table, the extension cannot contain a database link or a synonym that can be converted to a table with a database link.

  • If a partition extension or subpartition extension does not contain a synonym, you must use the base table to specify the partition extension or subpartition extension. Synonyms, views, or any other objects are not allowed.

  • In a PARTITION FOR or SUBPARTITION FOR clause, do not specify the DEFAULT keyword, MAXVALUE keyword, or a binding variable as the value of partition_key_value or subpartition_key_value.

  • In a PARTITION or SUBPARTITION clause, do not specify binding variables for a partition or subpartition name.

• You can perform only read operations such as SELECT on the objects in the recycle bin but cannot perform write operations such as DML and DDL operations on them.

• The SELECT...FOR UPDATE SKIP LOCKED statement supports only single-table queries.