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 primary keys
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 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.