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 data 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 a primary key must meet the following criteria:

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

• A primary key can contain at most 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 a primary key

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

In the following example, the table named student_table is created with no primary key.

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

In OceanBase Database, tables without a primary key use an auto-increment column as their hidden primary key. Auto-increment columns are globally unique across multiple partitions, and this characteristic is used to ensure that the hidden primary key is unique.

Auto-increment columns in OceanBase Database are compatible with the auto-increment module in MySQL and satisfy the following rules:

• Globally unique across multiple partitions.

• Continuous increase within a statement.

• Values generated are larger than values explicitly inserted by users.