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
nullor 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.