測試環境為 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)
沒有解決問題,試試搜尋本站其他內容