刪除主鍵並移除自動編號

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

   MariaDB root@(none):dyw> desc tbl;
   +-----------------+--------------+------+-----+---------+----------------+
   | Field           | Type         | Null | Key | Default | Extra          |
   +-----------------+--------------+------+-----+---------+----------------+
   | bi              | bigint(20)   | NO   |     | <null>  |                |
   | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
   | title           | varchar(100) | NO   |     | <null>  |                |
   | author          | varchar(40)  | NO   |     | <null>  |                |
   | submission_date | date         | YES  |     | <null>  |                |
   +-----------------+--------------+------+-----+---------+----------------+
   
   5 rows in set
   Time: 0.073s
   

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

   MariaDB root@(none):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.123s
   

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

   MariaDB root@(none):dyw> desc tbl;
   +-----------------+--------------+------+-----+---------+-------+
   | Field           | Type         | Null | Key | Default | Extra |
   +-----------------+--------------+------+-----+---------+-------+
   | bi              | bigint(20)   | NO   |     | <null>  |       |
   | id              | int(11)      | YES  |     | <null>  |       |
   | title           | varchar(100) | NO   |     | <null>  |       |
   | author          | varchar(40)  | NO   |     | <null>  |       |
   | submission_date | date         | YES  |     | <null>  |       |
   +-----------------+--------------+------+-----+---------+-------+
   
   5 rows in set
   Time: 0.024s
   

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

   MariaDB root@(none):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.142s
   

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

   MariaDB root@(none):dyw> desc tbl;
   +-----------------+--------------+------+-----+---------+----------------+
   | Field           | Type         | Null | Key | Default | Extra          |
   +-----------------+--------------+------+-----+---------+----------------+
   | bi              | bigint(20)   | NO   |     | <null>  |                |
   | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
   | title           | varchar(100) | NO   |     | <null>  |                |
   | author          | varchar(40)  | NO   |     | <null>  |                |
   | submission_date | date         | YES  |     | <null>  |                |
   +-----------------+--------------+------+-----+---------+----------------+
   
   5 rows in set
   Time: 0.023s