Tables with and without a primary key

OceanBase Database supports tables with and without a primary key.

Primary key

A primary key is a group of columns that uniquely identify a row in a database table. A primary key must meet the following criteria:

• It cannot contain a null or empty value.

• The values in the group of primary key columns must be unique within the entire table.

Tables with a primary key

In OceanBase Database, tables with primary keys must meet the following criteria:

• Each table can have up to one primary key column group.

• A primary key can contain up to 64 columns, and the total length of primary key data cannot exceed 16 KB.

When a table with a primary key is created, a globally unique index is created for the primary key columns to quickly locate rows through the primary key.

In the following example, the table named emp_table is created, with the emp_id column as its primary key.

CREATE TABLE emp_table (   
  emp_id INT PRIMARY KEY,   
  emp_name VARCHAR(100),   
  emp_age INT NOT NULL
);

Tables without primary keys

A table with no primary key specified is a table without a primary key.

In the following example, the student_table table without a primary key is created.

CREATE TABLE student_table (   
  student_id INT NOT NULL,   
  student_name VARCHAR(100),   
  student_age INT NOT NULL
);

In OceanBase Database, a partition-level auto-increment column is used as a hidden primary key for a table without a primary key to ensure that the values in this column are unique within each partition. However, the value of the partition-level auto-increment column obtained during an insert operation is not necessarily strictly incremental. This can happen due to concurrent inserts or uneven partition loads.