Shulou(Shulou.com)06/01 Report--

This article mainly explains "what is the difference between database primary key constraint, unique constraint and unique index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what is the difference between database primary key constraints, unique constraints and unique indexes?"

Primary key constraint (PRIMARY KEY)

Primary keys are used to uniquely identify each record in a table. You can define one or more columns of primary keys, and there can be only one primary key on a table.

Updating the primary key is not recommended

No two rows on the primary key column have the same value (that is, duplicate values), and null (NULL) is not allowed.

The main key can be used as external key, but the unique index is not allowed.

Uniqueness constraint (UNIQUE)

Uniqueness constraints are used to limit the uniqueness of data on columns that are not constrained by the primary key, and as an optional means of accessing a row, multiple uniqueness constraints can be placed on a table

As long as it is unique, it can be updated.

No two rows in the table are allowed to have the same value on the specified column, but null (NULL) is allowed.

Unique index (UNIQUE INDEX)

A unique index ensures that any attempt to generate duplicate key values will fail

As long as it is unique, it can be updated.

No two rows in the table are allowed to have the same value on the specified column, but null (NULL) is allowed.

The difference between a unique constraint and a primary key constraint:

The uniqueness constraint allows null values on the column, while the primary key constraint is more restrictive, allowing not only duplicates but also null values.

The difference between unique constraints and unique indexes:

The former is used to check the correctness of the data, and the latter is used to optimize the data query for different purposes.

Uniqueness constraints are different from unique indexes:

Creating a unique constraint creates a constraint in GBase 8s as well as a unique index corresponding to that constraint

Creating a unique index creates only one unique index, not a constraint

In other words, the only constraint is actually achieved by creating a unique index.

The following example illustrates the difference 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); insert into tab1 values (2); insert into tab1 values (3)

2, create primary keys, unique constraints, and unique indexes

The primary key is unique and null is not allowed, 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)

Looking at the report of the table, you can see three indexes (corresponding to the primary key, unique constraint and unique index), but it should be noted that the index name of the primary key and unique constraint is not the specified constraint name.

Oncheck-pt testdb:tab1 | grep 'fragment partition'Index 102' 3 fragment partition datadbs01 in DBspace datadbs01Index 102'4 fragment partition datadbs01 in DBspace datadbs01Index ux_tab1_col3 fragment partition datadbs01 in DBspace datadbs01

3. Test difference

Insert into tab1 values (null,'test004','test004')

Will report error: 703: Primary key on table (tab1) has a field with a null key value.

Null values are not allowed.

Insert into tab1 values (3 recordings test005)

Error will be reported: 268: Unique constraint (gbasedbt.pk_tab1_col1) violated.

The primary key constraint (pk_tab1_col1) is violated because a value of 3 already exists.

Insert into tab1 values (4 ~ test 006')

Error will be reported: 268: Unique constraint (gbasedbt.uk_tab1_col2) violated.

A unique constraint (uk_tab1_col2) is violated because the value null already exists.

Insert into tab1 values (5-minute test 003 minute test 007')

Error will be reported: 268: Unique constraint (gbasedbt.uk_tab1_col2) violated.

A unique constraint (uk_tab1_col2) is violated because the value test003 already exists.

Insert into tab1 values (6 minutes test 008 minutes null)

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 null already exists.

Insert into tab1 values (7 recordings test 009 minutes test 002')

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 minutes after test010)

Inserted successfully.

Alter table tab1 drop constraint uk_tab1_col2;oncheck-pt testdb:tab1 | grep 'fragment partition'Index 102' 3 fragment partition datadbs01 in DBspace datadbs01Index ux_tab1_col3 fragment partition datadbs01 in DBspace datadbs01

Delete the constraint and delete the corresponding index as well.

If you are careful, you will find:

If you add a not null constraint to a field, plus a unique constraint or unique index, it will be functionally equivalent to a primary key constraint.

Unique constraint has only one more constraint than unique index

At this point, I believe you have a deeper understanding of "what is the difference between database primary key constraints, unique constraints and unique indexes". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.