constraint —— Primary key constraint (primary key)

Statement , This article is only for learning notes , In view of —> Original address

stay MySQL in ,” constraint “ It refers to a restriction on the data in the table , It can ensure the accuracy and effectiveness of the data in the database
for example ： Student number 、 cell-phone number 、 ID number is the only value. , We need to restrict its uniqueness
stay MySql There are mainly 6 Medium constraint ： Primary key constraint 、 Foreign key constraints 、 Unique constraint 、 Check constraint 、 Non null and default constraints
Primary keys are divided into ” Single field primary key “ and ” Multi field union primary key “
When using primary keys, you should pay attention to the following points ：

• Only one primary key can be defined in a table
• The primary key value must uniquely identify each row in the table , And cannot be null, That is, there cannot be two or more rows of data with the same primary key in the table , Strictly observe The principle of uniqueness ;
• A field name can only appear once in the federated primary key field table ;
• A federated primary key cannot contain unnecessary fields . When a field in the union primary key is deleted , If the primary key composed of the remaining fields still meets the uniqueness principle , Then the joint primary key is wrong . This is a The principle of minimization .

1. Set primary key constraints when creating tables ( Single field primary key )

• Set primary key constraints when defining fields

create table < Data table name > (< Field name > < data type > primary key [ The default value is ]);

 for example ： Create a learning information data table in the database info_study, The primary key is _id,SQL The statement and running results are as follows ：
mysql> create table info_student (
    -> _id int(10) primary key,
    -> name varchar(20),
    -> class varchar(10),
    -> age int(2));
Query OK, 0 rows affected (0.03 sec)

• After defining all fields, specify the part as the primary key constraint ( Single field primary key / Combined the primary key )

create table < Data table name > (< Field name > < data type >...,primary key [ Field 1, Field 2...]);

mysql> create table boss_info(
    -> _id int(10),
    -> username varchar(50),
    -> phone_number varchar(100),
    -> primary key(_id,phone_number));
Query OK, 0 rows affected (0.02 sec)

Be careful ： When setting the federated primary key , Primary key constraints cannot be declared directly after each field .

2. When modifying a table, add a primary key constraint ( Can be more )

 Method 1：alter table < Data table name > modify < Field > < Field type > primary key;
 Method 2：alter table < Data table name > add primary key(< Field name >,< Field name >,...);

 Method 1：mysql> alter table personal modify _id int(10) primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

 Method 2：mysql> alter table personal add primary key(_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

 Be careful ：mysql> alter table personal add primary key(phone);
ERROR 1068 (42000): Multiple primary key defined
//  It should be noted that if there are constraints in the original table , Then the above error will be reported , Multiple primary key definitions 

3. Delete primary key constraint

alter table < Data table name > drop primary key;

mysql> alter table personal drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. How to delete a primary key constraint with self growth
   1、alter table < The name of the data table > modify column < Field name > < New type ( New length )>
2、alter table < The name of the data table > drop primary key;

mysql> alter table student_info modify column _id int(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student_info drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Use DOS The following error occurred: enter '; that will do

mysql> ' '>
    '> '> ';

summary

Primary key constraint is the most frequently used constraint , In general , A primary key will be set in the table
When creating a data table , In general , In order to easily look up the records in the table , Will ask to set up a ” Primary key “.
” Primary key “ Is a special field in the table , This field uniquely identifies each piece of information in the table .
Fields set as primary key constraints cannot have null values .