SQL – Primary & Unique

測試環境為 CentOS 8 x86_64

如果建立好的資料表沒有 Primary 或是 Unique ,在 phpMyAdmin 會顯示.

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

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

先建立一個資料庫 (testdb) , 與資料表 (employee) 格式為 Name CHAR(20), Dept CHAR(20), JobTitle CHAR(20) 各 20 個字元.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

下面是透過指令方式在資料表的資料欄位設定成為 Primary , Unique 但他們的差別是什麼呢!

  • Primary
    MariaDB [testdb]> CREATE TABLE employee (Name CHAR(20) , Dept CHAR(20) , JobTitle CHAR(20) , PRIMARY KEY (Name));
    Query OK, 0 rows affected (0.003 sec)
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   | PRI | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.001 sec)
    

    資料屌建立好之後可以透過 PHPMyAdmin 在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .或是透過 ALTER 指令來修改.

    MariaDB [testdb]> ALTER TABLE employee ADD PRIMARY KEY (Name);
    Query OK, 0 rows affected (0.013 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   | PRI | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.001 sec)
    

    Primary Key (主鍵) 必須為 not null (不能為空) 並且要 Unique (唯一).資料表只有一個欄位可以為 Primary Key.

    MariaDB [testdb]> 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 [testdb]> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('Ben','Testing','Engineer');
    Query OK, 1 row affected (0.00 sec)
    

    因為 Name 已經設定成為 Primary Key 所以 Name 欄位不能有重覆 (需 Unique 唯一), 可以使用 ” 空字串,不能為 NULL 空資料.

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

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

    MariaDB [testdb]> ALTER TABLE employee DROP PRIMARY KEY;
    Query OK, 2 rows affected (0.015 sec)              
    Records: 2  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   |     | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.002 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    Empty set (0.01 sec)
    

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

    MariaDB [testdb]> ALTER TABLE employee MODIFY Name CHAR(20) NULL;
    Query OK, 0 rows affected (0.015 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | YES  |     | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.002 sec)
    
  • Primary KEY (Auto Increment)
    如果沒有辦法從其中一個欄位來設定成為 primary ,這時候可以考慮新增一個欄位為 Primary 並設定成 AUTO_INCREMENT,AUTO INCREMENT PRIMARY KEY 欄位型態必須為整數類 (INT, TINYINT , SMALLINT , BIGINT 等…).

    MariaDB [testdb]> CREATE TABLE employee1 (Name CHAR(20),Dept CHAR(20),JobTitle CHAR(20));
    Query OK, 0 rows affected (0.008 sec)
    
    MariaDB [testdb]> ALTER TABLE employee1 ADD ID smallint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    Query OK, 0 rows affected (0.009 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  |     | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.002 sec)
    

    在 phpMyAdmin 一樣可以設定,只要在 Index 欄位選取 “PRIMARY”, 並勾選 A_I 欄即可

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

    MariaDB [testdb]> ALTER TABLE employee1 ADD UNIQUE (Name);
    Query OK, 0 rows affected (0.008 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  | UNI | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.001 sec)
    

    試試新增一些資料,當 Name 欄位資料重覆時無法輸入新資料.

    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES ('Tank','Sales','PM');
    Query OK, 1 row affected (0.002 sec)
    
    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES ('Tank','Engineer','Hardware');
    ERROR 1062 (23000): Duplicate entry 'Tank' for key 'Name'
    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES (NULL,'Engineer','Hardware');
    Query OK, 1 row affected (0.003 sec)
    

    移除 Unique

    MariaDB [testdb]> ALTER TABLE employee1 DROP INDEX Name;
    Query OK, 0 rows affected (0.006 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  |     | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.001 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料