next up previous contents
Next: Index 索引鍵 Up: KEY 及 INDEX Previous: 組合主鍵   Contents   DYWANG_HOME

Unique key 不重複鍵

  1. 查詢資料表 dcount 沒有索引。
    mariadb root@localhost:dyw> show index from dcount\G                            
    
    mariadb root@localhost:dyw>
    
  2. 先查詢資料表 dcount 欄位 author 沒有重複。
    mariadb root@localhost:dyw> select * from dcount;                               
    +--------+--------+
    | author | dcount |
    +--------+--------+
    | dywang | 4      |
    | jenny  | <null> |
    | linda  | 3      |
    | peter  | 2      |
    | rita   | 1      |
    | sara   | <null> |
    +--------+--------+
    
    6 rows in set
    Time: 0.026s
    
  3. 於資料表 dcount 加入一筆資料,欄位 author "dywang" 重複。
    mariadb root@localhost:dyw> insert into dcount(author,dcount) values ("dywang",1
                                2);                                                 
    Query OK, 1 row affected
    Time: 0.045s
    
  4. 再查詢資料表 dcount 欄位 author "dywang" 有重複。
    mariadb root@localhost:dyw> select * from dcount;                               
    +--------+--------+
    | author | dcount |
    +--------+--------+
    | dywang | 4      |
    | jenny  | <null> |
    | linda  | 3      |
    | peter  | 2      |
    | rita   | 1      |
    | sara   | <null> |
    | dywang | 12     |
    +--------+--------+
    
    7 rows in set
    Time: 0.026s
    
  5. 設定資料表 dcount 欄位 author 加入不重複鍵不成功,因為有資料重複。
    mariadb root@localhost:dyw> alter table dcount add unique key(author);          
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    (1062, "Duplicate entry 'dywang' for key 'author'")
    
  6. 刪除資料表 dcount 欄位 author 中重複的一筆資料。
    mariadb root@localhost:dyw> delete from dcount where dcount=12;                 
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 1 row affected
    Time: 0.005s
    
  7. 再設定資料表 dcount 欄位 author 不重複鍵,加入成功。
    mariadb root@localhost:dyw> alter table dcount add unique key(author);          
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.074s
    
  8. 查詢資料表 dcount index,其中 Non_qunique: 0 表示不重複。add unique (field) 與 add unique key (field) 結果一樣。
    mariadb root@localhost:dyw> show index from dcount\G                            
    ***************************[ 1. row ]***************************
    Table         | dcount
    Non_unique    | 0
    Key_name      | author
    Seq_in_index  | 1
    Column_name   | author
    Collation     | A
    Cardinality   | 6
    Sub_part      | <null>
    Packed        | <null>
    Null          | 
    Index_type    | BTREE
    Comment       | 
    Index_comment | 
    
    1 row in set
    Time: 0.014s
    
  9. 已無法於資料表 dcount 加入欄位 author 重複的資料。
    mariadb root@localhost:dyw> insert into dcount(author,dcount) values ("dywang",1
                                2);                                                 
    (1062, "Duplicate entry 'dywang' for key 'author'")
    
  10. 刪除資料表 dcount 的不重複鍵,因不重複鍵是索引,所以使用 drop index author
    mariadb root@localhost:dyw> alter table dcount drop index author;               
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 6 rows affected
    Time: 0.151s
    
  11. 查詢資料表 dcount 沒有索引。
    mariadb root@localhost:dyw> show index from dcount\G                            
    
    mariadb root@localhost:dyw>
    



De-Yu Wang 2020-04-07