目的:
解决单列主键无法保持唯一性的问题
例如:记录主机地址和服务的数据表是否运行的表。
![图示1](https://img.haomeiwen.com/i15572377/0c09670af73d8dfc.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
目标
表school.service
host_ip 主机IP
service_name 服务名
por 服务对应的端口
allow(Y,N) 服务是否允许访问
主键: host_ip + port = primary key
创建数据表
mysql> create table service(
host_ip varchar(15) not null,
service_name varchar(10) not null,
port varchar(5) not null,
allow enum('Y','N') default 'N',
primary key(host_ip,port)
);
Query OK, 0 rows affected (0.00 sec)
(关键部分,复合主键设置方法
primary key(host_ip,port))
查看表结构
mysql> desc service;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| host_ip | varchar(15) | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
| port | varchar(5) | NO | PRI | NULL | |
| allow | enum('Y','N') | YES | | N | |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
![图示2](https://img.haomeiwen.com/i15572377/7373d46599a4bef1.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
插入数据
mysql> insert into service values ('192.168.2.168','ftp','21','Y');
mysql> insert into service values ('192.168.2.168','httpd','80','Y');
总结:
复合主键是当多列组合唯一
案例:
在mysql系统内部对于mysql账户的记录就是复合主键(用户名+主机地址)
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
网友评论