next up previous contents
Next: Unique key 不重複鍵 Up: KEY 及 INDEX Previous: Primary key 主鍵   Contents   DYWANG_HOME

組合主鍵

  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 = wangfirst_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 = wangfirst_name = dalast_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 = linfirst_name = dalast_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'")
    



De-Yu Wang 2020-04-07