559 瀏覽數

DataBase Primary , Unique , Index Key

透過 phpMyAdmin 轉好的資料表會顯示

這個資料表中沒有可以辦識(unique)的資料欄位 將無法執行修改、複製、刪除等相關的功能。

我們需要在 資料表 / 結構 的其中之一的資料欄位設定成為 unique .或是 Primary Key (主鍵) – 必須為 not null (不能為空) 並且要 unique (唯一) .

設定好之後就可以在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .

在資料表中結構裡面的資料欄位 可以設定成為 Primary , Unique , Index Key 他們的差別是什麼呢!

先建立一個資料庫 (sbtest) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元.

root@ubuntu:~# mysql -u root -p
MariaDB [(none)]> create database sbtest;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> use sbtest;
Database changed
 
MariaDB [sbtest]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.10 sec)
 
MariaDB [sbtest]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
 
MariaDB [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

Primary

Primary Key (主鍵) 必須為 not null (不能為空) 並且要 unique (唯一).資料表只有一個欄位可以為 Primary Key.
剛剛在建立資料表的時候並沒有設定 Primary Key,可以透過 alter 來修改.

MariaDB [sbtest]> alter table employee add primary key(Name);
Query OK, 4 rows affected (0.03 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | NO   | PRI |         |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

alter 的用法

ALTER TABLE table_name ...;

ADD – 增加欄位 (ADD COLUMN) 或是增加欄位的 Key (Primary , Unique , Index Key)
DROP – 刪除欄位 (DROP COLUMN) 或是刪除欄位的 Key (Primary , Unique , Index Key)
CHANGE – 修改欄位名稱.
MODIFY – 修改欄位的資料類別,或可否為 NULL.

MariaDB [sbtest]> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee |          0 | PRIMARY  |            1 | Name        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

來試試看新增一些資料.

MariaDB [sbtest]> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.00 sec)

因為 Name 已經設定成為 primary key 所以重覆的名稱不能使用(需 unique 唯一),但是”代表的是空字串,並不是 NULL(沒有資料).

MariaDB [sbtest]> INSERT INTO employee VALUES ('Ben','Sales','Manager');
ERROR 1062 (23000): Duplicate entry 'Ben' for key 'PRIMARY'
MariaDB [sbtest]> INSERT INTO employee VALUES ('','Sales','Manager');
Query OK, 1 row affected (0.00 sec)
MariaDB [sbtest]> INSERT INTO employee VALUES (NULL,'Sales','Manager');
ERROR 1048 (23000): Column 'Name' cannot be null

要移除 Primary key ,一樣是透過 alter 指令.因為資料表中只能有一個欄位設定成為 Primary Key ,所以在 drop 移除時就不需要再指定了.

MariaDB [sbtest]> alter table employee drop primary key;
Query OK, 3 rows affected (0.06 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | NO   |     |         |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.07 sec)

MariaDB [sbtest]> show index from employee;
Empty set (0.01 sec)

但欄位還是不能 NULL ,一樣是透過 alter 指令修改.

MariaDB [sbtest]> alter table employee modify name char(20) NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Unique

Unique Key (唯一鍵) 不能重覆,但是可以為 null (空)

MariaDB [sbtest]> alter table employee add unique (Name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(20) | YES  | UNI | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

試試新增一些資料.

MariaDB [sbtest]> INSERT INTO employee VALUES ('Tank','Sales','PM');
Query OK, 1 row affected (0.00 sec)

MariaDB [sbtest]> INSERT INTO employee VALUES ('Tank','Engineer','Hardware');
ERROR 1062 (23000): Duplicate entry 'Tank' for key 'name'
MariaDB [sbtest]> INSERT INTO employee VALUES (NULL,'Engineer','Hardware');
Query OK, 1 row affected (0.00 sec)

移除 unique

MariaDB [sbtest]> alter table employee drop index Name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Index Key

Index Key (索引鍵) ,常做存取的的欄位可以設定成為索引,這樣可以增加搜尋速度.

MariaDB [sbtest]> alter table employee add index (Name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(20) | YES  | MUL | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

移除 index

MariaDB [sbtest]> alter table employee drop index Name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sbtest]> describe employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

可以看到 UNI (Unique) 跟 MUL (Index) 的差別是,Unique 不能重覆, Index 可以重覆.

發表迴響