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, tables without a primary key use a partition-level auto-increment column as their hidden primary key.
In OceanBase Database, a partition-level auto-increment column ensures the value uniqueness in each partition, but does not guarantee that the partition-level auto-increment column is also strictly auto incrementing when data is inserted.