PRIMARY KEY constraints

A PRIMARY KEY constraint imposes a primary key value rule on a key, which can be a column or set of columns. This rule ensures that the values in the group of one or more columns subject to the constraint uniquely identify the row.

You can define only one PRIMARY KEY constraint in a table. The values of the primary key column or columns can be used as the unique identifiers of their corresponding rows. Therefore, each row can be named by a primary key value.

Note

OceanBase Database allows you to create a primary key by executing the CREATE TABLE statement when you create a table. You cannot add, delete, or modify the primary key by executing the ALTER TABLE statement.

The following example shows the features of the syntax of a PRIMARY KEY constraint.

obclient> CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT pk_c1_c2 PRIMARY KEY(c1, c2));
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 row affected 

obclient> INSERT INTO t1 VALUES(1, 1);
ORA-00001: unique constraint '1-1' for key 'PK_C1_C2' violated

obclient> INSERT INTO t1 VALUES(null, 1);
ORA-01400: cannot insert NULL into '(C1)'

obclient> INSERT INTO t1 VALUES(null, null);
ORA-01400: cannot insert NULL into '(C1)'

A PRIMARY KEY constraint ensures that data in a table meets the following requirements:

• No two rows of a table have duplicate values in the specified primary key column or columns.

• The primary key columns do not contain nulls. Therefore, you must ensure that a value exists for the primary key columns in each row.

Although primary keys are not required, we recommend that you define a primary key for every table. This ensures that each row can be uniquely identified and that no duplicate rows exist in a table.

The following figure shows the PRIMARY KEY constraint defined for the dept table and the rows that violate this constraint. The dept table contains the deptno, dname, and loc columns. The deptno column is defined as the primary key of the dept table. Therefore, no duplicate or null values can exist in the deptno column.

In the following figure, two rows are prevented from being inserted into the dept table because they violate the PRIMARY KEY constraint. One row has the same value as that of an existing row in the deptno column and the other has a null value in the column.