組合主鍵

1. 產生資料表 employees，有 id, last_name, first_name, phone 四個欄位，其中 last_name, first_name 兩個欄位組合成 primary key。

   mariadb root@localhost:dyw> create table employees(id int unsigned not null,last
                               _name varchar(20),first_name varchar(20),primary key
                               (last_name,first_name),phone varchar(20));          
   Query OK, 0 rows affected
   Time: 0.057s
   

2. 列出資料表 employees 的所有欄位特徵。

   mariadb root@localhost:dyw> desc employees;                                     
   +------------+------------------+------+-----+---------+-------+
   | Field      | Type             | Null | Key | Default | Extra |
   +------------+------------------+------+-----+---------+-------+
   | id         | int(10) unsigned | NO   |     | <null>  |       |
   | last_name  | varchar(20)      | NO   | PRI | <null>  |       |
   | first_name | varchar(20)      | NO   | PRI | <null>  |       |
   | phone      | varchar(20)      | YES  |     | <null>  |       |
   +------------+------------------+------+-----+---------+-------+
   
   4 rows in set
   Time: 0.024s
   

3. employees 增加兩筆資料。

   mariadb root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                               ) values (1,'wang','dy','09123456'), (2,'lin','da','
                               09234561');                                         
   Query OK, 2 rows affected
   Time: 0.006s
   

4. 查詢 employees 有兩筆資料。

   mariadb root@localhost:dyw> select * from employees;                            
   +----+-----------+------------+----------+
   | id | last_name | first_name | phone    |
   +----+-----------+------------+----------+
   | 2  | lin       | da         | 09234561 |
   | 1  | wang      | dy         | 09123456 |
   +----+-----------+------------+----------+
   
   2 rows in set
   Time: 0.024s
   

5. 再增加一筆 last_name = wang，first_name = da。

   mariadb root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                               ) values (1,'wang','da',null);                      
   Query OK, 1 row affected
   Time: 0.006s
   

6. last_name = wang，first_name = da，last_name, first_name 都與現有資料重複，但合在一起沒有重複，所以成功加入。

   mariadb root@localhost:dyw> select * from employees;                            
   +----+-----------+------------+----------+
   | id | last_name | first_name | phone    |
   +----+-----------+------------+----------+
   | 2  | lin       | da         | 09234561 |
   | 1  | wang      | da         | <null>   |
   | 1  | wang      | dy         | 09123456 |
   +----+-----------+------------+----------+
   
   3 rows in set
   Time: 0.027s
   

7. 再增加一筆 last_name = lin，first_name = da，last_name, first_name 組合與現有資料重複，所以無法加入。

   mariadb root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                               ) values (1,'lin','da',null);                       
   (1062, "Duplicate entry 'lin-da' for key 'PRIMARY'")