Unique key 不重複鍵

1. 查詢資料表 dcount 沒有索引。

   MariaDB root@(none):dyw> show index from dcount\G
   0 rows in set
   Time: 0.024s
   

2. 先查詢資料表 dcount 欄位 author 沒有重複。

   MariaDB root@(none):dyw> select * from dcount;
   +--------+--------+
   | author | dcount |
   +--------+--------+
   | dywang | 4      |
   | jenny  | <null> |
   | linda  | 3      |
   | peter  | 2      |
   | rita   | 1      |
   | sara   | <null> |
   +--------+--------+
   
   6 rows in set
   Time: 0.024s
   

3. 於資料表 dcount 加入一筆資料，欄位 author "dywang" 重複。

   MariaDB root@(none):dyw> insert into dcount(author,dcount) values('dywang',12);
   Query OK, 1 row affected
   Time: 0.009s
   

4. 再查詢資料表 dcount 欄位 author "dywang" 有重複。

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

5. 設定資料表 dcount 欄位 author 加入不重複鍵不成功，因為有資料重複。

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

7. 再查詢資料表 dcount 欄位 author，已無重複紀錄。

   MariaDB root@(none):dyw> select * from dcount;
   +--------+--------+
   | author | dcount |
   +--------+--------+
   | dywang | 4      |
   | jenny  | <null> |
   | linda  | 3      |
   | peter  | 2      |
   | rita   | 1      |
   | sara   | <null> |
   +--------+--------+
   
   6 rows in set
   Time: 0.022s
   

8. 再設定資料表 dcount 欄位 author 不重複鍵，加入成功。

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

9. 查詢資料表 dcount index，其中 Non_unique: 0 表示不重複。add unique (field) 與 add unique key (field) 結果一樣。

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

10. 已無法於資料表 dcount 加入欄位 author 重複的資料。

    MariaDB root@(none):dyw> insert into dcount(author,dcount) values('dywang',12);
    (1062, "Duplicate entry 'dywang' for key 'author'")
    

11. 刪除資料表 dcount 的不重複鍵，因不重複鍵是索引，所以使用 drop index author。

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

12. 查詢資料表 dcount 沒有索引。

    MariaDB root@(none):dyw> show index from dcount\G
    0 rows in set
    Time: 0.004s