PRIMARY KEY constraint

1. Primary key is used to uniquely identify each record in a table. You can define one or more columns as primary keys. There can only be one primary key on a table;
2. It is not recommended to update the primary key;
3. No two rows on the primary key column have the same value (i.e. duplicate value), and NULL (NULL) is not allowed;
4. The main key can be used as external key, and the unique index cannot be used;

Uniqueness constraint (UNIQUE)

1. Uniqueness constraints are used to restrict the uniqueness of data on columns that are not subject to primary key constraints. As an optional means to access a row, multiple uniqueness constraints can be placed on a table;
2. As long as it is unique, it can be updated;
3. Any two rows in the table are not allowed to have the same value on the specified column, but NULL is allowed;

UNIQUE INDEX

1. A unique index ensures that any attempt to generate duplicate key values will fail;
2. As long as it is unique, it can be updated;
3. Any two rows in the table are not allowed to have the same value on the specified column, but NULL is allowed;

The difference between uniqueness constraint and primary key constraint:

1. The uniqueness constraint allows NULL values on this column, while the primary key constraint is more restrictive. Not only duplication, but also NULL values are not allowed.

The difference between unique constraint and unique index:

The former is used to check the correctness of data, and the latter is used to optimize data query for different purposes.
Uniqueness constraints are different from unique indexes:

1. Creating a unique constraint will create a constraint in GBase 8s and a unique index corresponding to the constraint;
2. Creating a unique index will only create a unique index, not a constraint;
   In other words, the unique constraint is realized by creating a unique index.

The following examples illustrate the differences between the three:
1. Create tables and import basic data

drop table if exists tab1;
create table tab1(col1 int, col2 varchar(20), col3 varchar(20));
insert into tab1 values(1,'test001','test001');
insert into tab1 values(2,null,'test002');
insert into tab1 values(3,'test003',null);

2. Create primary key, unique constraint and unique index
The primary key is unique and cannot be null, the unique constraint value is unique but null is allowed, and the unique index value is unique but null is allowed

alter table tab1 add constraint primary key(col1) constraint pk_tab1_col1;
alter table tab1 add constraint unique(col2) constraint uk_tab1_col2;
create unique index ux_tab1_col3 on tab1 (col3);

Viewing the report of the table, you can see three indexes (corresponding to primary key, unique constraint and unique index), but it should be noted that the index names of primary key and unique constraint are not the specified constraint names

oncheck -pt testdb:tab1 | grep 'fragment partition'

Index  102_3 fragment partition datadbs01 in DBspace datadbs01
Index  102_4 fragment partition datadbs01 in DBspace datadbs01
Index ux_tab1_col3 fragment partition datadbs01 in DBspace datadbs01

3. Test difference

insert into tab1 values(null,'test004','test004');

An error will be reported: 703: Primary key on table (tab1) has a field with a null key value
Null values are not allowed.

insert into tab1 values(3,'test005','test005');

An error will be reported: 268: Unique constraint (gbasedbt.pk_tab1_col1) violated
The primary key constraint (pk_tab1_col1) was violated because the value 3 already exists.

insert into tab1 values(4,null,'test006');

An error will be reported: 268: Unique constraint (gbasedbt.uk_tab1_col2) violated
The unique constraint (uk_tab1_col2) was violated because the value null already exists.

insert into tab1 values(5,'test003','test007');

An error will be reported: 268: Unique constraint (gbasedbt.uk_tab1_col2) violated
The unique constraint (uk_tab1_col2) is violated because the value test003 already exists.

insert into tab1 values(6,'test008',null);

An error will be reported: 239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ux_tab1_col3)
Inserting duplicate values is not allowed because the value null already exists.

insert into tab1 values(7,'test009','test002');

An error will be reported: 239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ux_tab1_col3)
Duplicate values are not allowed to be inserted because the value test002 already exists.

insert into tab1 values(8,'test010','test010');

Insert succeeded.

alter table tab1 drop constraint uk_tab1_col2;

oncheck -pt testdb:tab1 | grep 'fragment partition'

Index  102_3 fragment partition datadbs01 in DBspace datadbs01
Index ux_tab1_col3 fragment partition datadbs01 in DBspace datadbs01

Delete the constraint and the corresponding index.

If you are careful, you will find:

1. If you add a not null constraint to a field, and add a unique constraint or unique index, the function will be equivalent to the primary key constraint;
2. The unique constraint is only one more constraint than the unique index;