gogoWebsite

Primary Key Constraint (PRIMARY KEY) [MySQL][Database]

Updated to 2 days ago

Primary Key Constraint (PRIMARY KEY)

Features of primary key constraints:

  1. Primary key constraints are equivalent to unique constraints + non-null constraints. Primary key constraints do not allow duplication and null values.

  2. A table can only have one primary key constraint at most. You can create a primary key constraint at the column level or at the table level.

  3. Primary key constraints correspond to one or more columns in the table

    • When it corresponds to multiple columns, it is the compound primary key (it can be compared to the compound uniqueness constraints we mentioned before)

  4. If it is a composite primary key constraint of multiple columns, none of these columns are allowed to be null values, and the combined values ​​are not allowed to be repeated.

  5. The primary key constraint name in MySQL is always PRIMARY. Even if we name the primary key constraint name ourselves, the primary key constraint name is still PRIMARY at this time and will not change.

  6. When creating a primary key constraint, the system will establish the corresponding primary key index on the column or column combination where the primary key constraint is located by default. If the primary key constraint is deleted, the primary key index will be automatically deleted.

    • When there is a primary key constraint in a table, the system will default to construct a B+tree structure based on the primary key index corresponding to the primary key constraint, which is used to store data in the table. The query efficiency is higher under this B+tree structure

  7. Note: Do not modify the value of the primary key field, because the primary key is the unique identifier of the data record. If the value of the primary key is modified, it may destroy the integrity of the data.

    • Regarding the integrity of data, we have mentioned in the constraint overview. Data integrity can be discussed in four aspects.

How to add primary key constraints?

We also divide the primary key constraint into two ways (in fact, we can find that there are two situations when we add constraints. One is to add it when CREATE TABLE, and the other is to add it when ALTER TABLE, and when deleting constraints, they are to delete constraints.)

Method 1: Add primary key constraints when CREATE TABLE

Let's learn how to add primary key constraints by giving examples

CREATE TABLE test4(
id  INT PRIMARY KEY,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
  • Here we add a column-level primary key constraint to the id field
  • Primary key constraint: non-empty and unique, used to uniquely identify a record in the table
    • Why is it a record in the unique identification table?
      If it is a unique constraint, then the value of the field cannot be repeated, but there may be multiple nulls, because there can be multiple nulls in our unique constraint. Then, after we add a non-null constraint, it can only be the only value, and it cannot be null. So we also say that the primary key constraint is a unique constraint + non-null constraint
We can only add one primary key constraint to a table. If we add multiple primary key constraints to the table, an error will be reported at this time:

eg:

CREATE TABLE test5(
id INT PRIMARY KEY,
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2)
);
How to create composite primary key constraints?

Here we will illustrate by giving examples

CREATE TABLE test6(
 id INT,
 last_name VARCHAR(15),
 salary DECIMAL(10),
 email VARCHAR(25),
 #Here we add a table-level constraint and a compound constraint at the same time. Here we add primary key constraints to the id field and the last_name field at the same time
 CONSTRAINT pk_test6_id_name PRIMARY KEY(id,last_name)
 );
  • We created a composite primary key constraint above, constraining both the id field and the last_name field.
  • We also use the constraint and keyword to try to name this table-level compound primary key constraint, but we said before: In MySQL, the primary key constraint name is always PRIMARY and cannot be changed. If you change, you will find that the primary key constraint name is still PRIMARY after the change.
So what is the effect after adding the primary key constraint? Here we will do a test
INSERT INTO test6
VALUES
(1,'tom',3000,'tom@'),
(2,'tom',3000,'tom@');
  • At this time, we can find that the data was added successfully, which means that as long as the field values ​​in the composite column are not the same in our composite primary key constraint, it is not considered duplicate at this time. At this time, it can be added successfully. For example, here: The last_name of the two added records is the same, but the ids of the two records are different. Even if it is not repeated at this time, it can be added successfully
INSERT INTO test6
VALUES 
(NULL,'tom',3000,'tom@');
  • At this time, we can find that our record has failed to add. Why? Our primary key constraint is equivalent to a unique constraint + non-null constraint. For the uniqueness of our primary key, we require that the value of the compound field be different as long as it is different. However, for non-nullity, as long as one of the values ​​in the compound field is null, the data will be added to the data.
Summary: If it is a composite primary key constraint for multiple column combinations, then there is no null value allowed in these columns, and the combined values ​​in these columns are not allowed to be repeated.

Method 2: Add primary key constraints when ALTER TABLE

First, we will create a new table, and then add primary key constraints in this new table through ALTER TABLE

CREATE TABLE test7(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
  • Here we create a test7 table, and no primary key constraint is added to this test7 table

Here we add primary key constraints through the ALTER TABLE operation

ALTER TABLE test6
ADD PRIMARY KEY(id);
  • Here we add a primary key constraint to the id field

So how do we remove primary key constraints?

Let's give an example here (ALTER TABLE)

ALTER TABLE test6
DROP PRIMARY KEY;
  • Since there is only one primary key constraint in one table, we do not need to specify the primary key name when deleting the primary key constraint
Note: After we delete the primary key constraint, the non-null constraint is still there, which means that non-null constraints will still be retained. That is, after we delete the primary key constraints, we still need to pay attention: At this time, we still cannot add null values ​​to this field that has set the primary key constraints.

Replenish:

In actual programming, we will not delete the primary key constraint, because at the bottom of the database, data will be stored in B+tree. If there is a primary key constraint, the primary key index will be used as the storage sentence of B+tree. If there is no primary key, the index corresponding to other types of constraints will be used as the basis for storage of B+tree. If there is no constraint, the system will specify a field as the basis. Then if we have primary key constraints, after we delete this primary key constraint, then tens of millions of records in the table need to be stored again, because at this time, the primary key index will be deleted after the primary key constraint is deleted, so no one will delete the primary key index in actual programming.