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 value or be empty.
- The values in the group of primary key columns must be unique within the entire table.
- The values of the primary key cannot be modified.
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 16KB.
When a table with a primary key is created, a globally unique index will be created for the primary key columns to quickly locate rows through the primary key.
The following SQL statement is an example that creates a table named emp_table, with emp_id 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. Due to the lack of a global index, ROWID is often used to quickly locate a row in a table with no primary key.
The following SQL statement is an example that creates a table without a primary key named student_table.
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 are implemented with an auto-increment column as their hidden 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 explicit insertion of users
The globally unique across multiple partitions characteristic of auto-increment columns are used to ensure the hidden primary key are unique.