PRIMARY KEY constraints

The PRIMARY KEY constraint enforces a primary key value rule on a key, which can be a single column or a set of columns. This rule guarantees that the values within the specified column or columns uniquely identify each row in the table.

Only one PRIMARY KEY constraint can be defined on each database table. The values of the columns (one or multiple columns) that make up this constraint can serve as a unique identifier for a row of data, naming each data row with this primary key value.

This topic introduces the features of the PRIMARY KEY constraint through the following specific examples.

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);
OBE-00001: unique constraint '1-1' for key 'PK_C1_C2' violated

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

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

The PRIMARY KEY integrity constraint ensures that the data in a table adheres to the following two rules:

• No duplicate values are allowed in the columns defined as the PRIMARY KEY constraint, whether it is a single column or a set of columns.

• The values in the primary key columns cannot be empty or NULL. Users are required to input a value for the primary key columns.

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 diagram below illustrates the PRIMARY KEY constraint defined on the DEPT table and the rows that violate this constraint. The DEPT table contains three columns: DEPTNO, DNAME, and LOC. The primary key constraint is defined on the DEPTNO column, which means this column cannot have duplicate values and cannot be empty.

The diagram also shows two rows of data that cannot be inserted into the DEPT table due to a violation of the primary key constraint. One row has a primary key value that already exists in the table, and the other row has an empty value in the primary key column.