next up previous contents
Next: 實機操作練習題 Up: KEY 及 INDEX Previous: Sequence 序列   Contents   DYWANG_HOME

刪除主鍵並移除自動編號

  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
    



De-Yu Wang 2020-04-07