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
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
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
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
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
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
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'")