組合主鍵

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

   MariaDB root@(none):dyw> create table employees(id int unsigned not null,last_nam
                         -> e varchar(20),first_name varchar(20),primary key(last_na
                         -> me,first_name),phone varchar(20));
   Query OK, 0 rows affected
   Time: 0.132s
   

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

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

3. employees 增加兩筆資料。

   MariaDB root@(none):dyw> insert into employees (id,last_name,first_name,phone) va
                         -> lues (1,'wang','dy','09123456'), (2,'lin','da','09234561
                         -> ');
   Query OK, 2 rows affected
   Time: 0.024s
   

4. 查詢 employees 有兩筆資料。

   MariaDB root@(none):dyw> select * from employees;
   +----+-----------+------------+----------+
   | id | last_name | first_name | phone    |
   +----+-----------+------------+----------+
   | 2  | lin       | da         | 09234561 |
   | 1  | wang      | dy         | 09123456 |
   +----+-----------+------------+----------+
   
   2 rows in set
   Time: 0.028s
   

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

   MariaDB root@(none):dyw> insert into employees (id,last_name,first_name,phone) va
                         -> lues (1,'wang','da',null);
   Query OK, 1 row affected
   Time: 0.011s
   

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

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

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

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