刪除主鍵並移除自動編號

1. 查詢資料表 tbl，欄位 id 是 primary key，且自動編號 (auto_increment)。

   mariadb root@localhost:dyw> desc tbl;                                           
   +-----------------+--------------+------+-----+---------+----------------+
   | Field           | Type         | Null | Key | Default | Extra          |
   +-----------------+--------------+------+-----+---------+----------------+
   | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
   | title           | varchar(100) | NO   |     | <null>  |                |
   | author          | varchar(40)  | NO   |     | <null>  |                |
   | submission_date | date         | YES  |     | <null>  |                |
   +-----------------+--------------+------+-----+---------+----------------+
   
   4 rows in set
   Time: 0.026s
   

2. 刪除 Primary key 或移除 auto_increment 特徵，兩者都無法單獨刪除或變更，必須於刪除 primary key 時，同時變更 id 的欄位特徵。

   mariadb root@localhost:dyw> alter table tbl drop primary key,change id id int(11
                               );                                                  
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 4 rows affected
   Time: 0.039s
   

3. 再查看資料表 tbl 的欄位 id 設定，沒有 key 且沒有 auto_increment 特徵。

   mariadb root@localhost:dyw> desc tbl;                                           
   +-----------------+--------------+------+-----+---------+-------+
   | Field           | Type         | Null | Key | Default | Extra |
   +-----------------+--------------+------+-----+---------+-------+
   | id              | int(11)      | YES  |     | <null>  |       |
   | title           | varchar(100) | NO   |     | <null>  |       |
   | author          | varchar(40)  | NO   |     | <null>  |       |
   | submission_date | date         | YES  |     | <null>  |       |
   +-----------------+--------------+------+-----+---------+-------+
   
   4 rows in set
   Time: 0.027s
   

4. 再同時增加 Primary key 並設定自動編號 auto_increment 特徵。

   mariadb root@localhost:dyw> alter table tbl add primary key(id),change id id int
                               (11) auto_increment;                                
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 4 rows affected
   Time: 0.037s
   

5. 再查看資料表 tbl 的欄位 id 設定，又是 primary key 且有 auto_increment 特徵。

   mariadb root@localhost:dyw> desc tbl;                                           
   +-----------------+--------------+------+-----+---------+----------------+
   | Field           | Type         | Null | Key | Default | Extra          |
   +-----------------+--------------+------+-----+---------+----------------+
   | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
   | title           | varchar(100) | NO   |     | <null>  |                |
   | author          | varchar(40)  | NO   |     | <null>  |                |
   | submission_date | date         | YES  |     | <null>  |                |
   +-----------------+--------------+------+-----+---------+----------------+
   
   4 rows in set
   Time: 0.024s