OceanBase Database supports tables with and without a primary key. This topic describes primary keys and the rules for using tables with and without primary keys.
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 a primary key
A table with no specified primary key is a table without a primary key. Due to the lack of a global index, ROWID is often used to quickly locate a row in 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 an auto-increment column as their hidden primary key. An auto-increment column is globally unique across multiple partitions. This characteristic ensures that the invisible primary key is unique in a table without a primary key.
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.