MySQL [04] A problem with primary key replacement

SQL syntax for primary keys:
Alter table t_google_mem drop primary key; // The primary key is displayed for deletion.
Alter table t_google_mem add primary key (f_id); // create a primary key
Alter table t_google_mem add primary key (f_id, f_csname); // create a multi-value primary key

The following describes a problem I encountered during my work. Currently, I have the following table. The operation I want to do now is to combine f_id and f_csname as multi-value primary keys.

 
Mysql> DESC t_google_mem; + -------------- + ----------- + ------ + ----- + upper + ---------------- + | FIELD | type | null | key | default | extra | + -------------- + ----------- + ------ + ----- + upper + ---------------- + | f_id | int (10) | no | pri | null | auto_increment | f_name | char (255) | no | f_assion | char (255) | no | f_csname | char (255) | no | f_time | datetime | no | 0000-00-00 00:00:00 | f_status | int (10) | no | 1 | + -------------- + ----------- + ------ + ----- + ------------------- + ---------------- +
 

Incorrect practice:

According to my previous habits, I planned to delete the table's primary key first and then add a multi-value primary key. However, when the alter table t_google_mem drop primary key; statement is used, the following error occurs:

Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Incorrect table definition: Incorrect table definition. Only one field can be used as auto increment and must be used as the primary key.

After thinking about the above error message, I think the correct step should first remove the auto_increment attribute of the f_id field in the table, then delete the primary key, and then add the multi-value primary key, then, adjust f_id to auto_increment.

Let's just do it. Next we will begin to implement SQL modification.

Step 1: alter table t_google_mem modify f_id int (10) Not null default 0;

 
Mysql> alter table t_google_mem modify f_id int (10) Not null default 0; query OK, 41 rows affected (0.05 Sec) records: 41 duplicates: 0 Warnings: 0
 

Step 2: alter table t_google_mem drop primary key;

 
Mysql> alter table t_google_mem drop primary key; query OK, 41 rows affected (0.04 Sec) records: 41 duplicates: 0 Warnings: 0
 

Step 3: alter table t_google_mem add primary key (f_id, f_csname );

 
Mysql> alter table t_google_mem add primary key (f_id, f_csname); query OK, 41 rows affected (0.03 Sec) records: 41 duplicates: 0 Warnings: 0
 

Step 4: alter table t_google_mem modify f_id int (10) Not null auto_increment;

 
Mysql> alter table t_google_mem modify f_id int (10) Not null auto_increment; query OK, 41 rows affected (0.02 Sec) records: 41 duplicates: 0 Warnings: 0
 

After the preceding four steps are modified, the checklist structure is displayed and meets my requirements.

 mysql> DESC t_google_mem; + -------------- + ----------- + ------ + ----- + upper + ---------------- + | FIELD | type | null | key | default | extra | + -------------- + ----------- + ------ + ----- + upper + ---------------- + | f_id | int (10) | no | pri | null | auto_increment | f_name | char (255) | no | f_assion | char (255) | no | f_csname | char (255) | no | pri | f_time | datetime | no | 0000-00-00 00:00:00 | f_status | int (10) | no | 1 | + -------------- + ----------- + ------ + ----- + ------------------- + ---------------- +