Mysql-表的完整性约束
一、概述
为了约束用户对数据增,删,改,以确保数据正确,有效,合规。
有以下几种约束
- not null 非空 指定某列不能为空
- unique 唯一 指定某列或某几列的组合不能重复
- primary key 主键 指定某列的值可以唯一标识该列记录
- foreign key 外键 指定该行记录从属于主表的某条记录,主要用于参照完整性
二、not null
- null 某列可以为空
- not null 某列不可以为空
2.1 实例1
mysql> create table t2(id int not null) engine=innodb default charset = utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> insert into t2(id) values(2); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) # 插入空值会报错 mysql> insert into t2(id) values(null); ERROR 1048 (23000): Column 'id' cannot be null
2.2 与default 混用
mysql> create table t3(id1 int not null,id2 int not null default 10)engine = innodb default charset = utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc t3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 10 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
# 如果不指定默认列值,则使用默认值 mysql> insert into t3(id1) values(1); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; +-----+-----+ | id1 | id2 | +-----+-----+ | 1 | 10 | +-----+-----+ 1 row in set (0.00 sec)
# 向默认值列插入数据,新数据会覆盖默认数据 mysql> insert into t3(id1,id2) values(12,12); Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +-----+-----+ | id1 | id2 | +-----+-----+ | 1 | 10 | | 12 | 12 | +-----+-----+
三、unique
指定某列或某几列不能重复
3.1 创建唯一索引的两种方式
# 方法1 mysql> create table userinfo1( -> id int auto_increment primary key, -> name varchar(20) not null unique, -> password varchar(100) not null) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> desc userinfo1 -> ; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | UNI | NULL | | | password | varchar(100) | NO | | NULL | |
方法2:
mysql> create table userinfo2( -> id int auto_increment primary key, -> name varchar(20), -> password varchar(100), -> unique(name)) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> desc userinfo2; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | UNI | NULL | | | password | varchar(100) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
mysql> desc userinfo2;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | UNI | NULL | |
| password | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 向唯一索引所在的列进行插入数据时,如果数据相同,则会报错
mysql> insert into userinfo2(name,password) values('wangys','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into userinfo2(name,password) values('wangys','123');
ERROR 1062 (23000): Duplicate entry 'wangys' for key 'name'
3.2 联合唯一索引
多列的组合唯一
mysql> create table service( -> id int auto_increment primary key , -> name varchar(20) not null, -> host varchar(32) not null, -> port int not null, -> unique(host,port)) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> desc service; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | host | varchar(32) | NO | MUL | NULL | | | port | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into service(name,host,port) values('nginx','1.1.1.1',80); Query OK, 1 row affected (0.00 sec) mysql> insert into service(name,host,port) values('nginx','1.1.1.1',80); ERROR 1062 (23000): Duplicate entry '1.1.1.1-80' for key 'host' mysql> insert into service(name,host,port) values('nginx','1.1.1.1',90); Query OK, 1 row affected (0.00 sec)
四、primary key
- 唯一值
- 非空
- 可以为一列,也可以为多列(联合主键)
- 可以再创建表时创建,也可以基于已创建的表进行增加
4.1 创建主键的方法
第一种
mysql> create table userinfo( -> id int auto_increment primary key, -> name varchar(20)) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.09 sec) mysql> desc userinfo -> ; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
第二种
mysql> create table userinfo2( -> id int auto_increment, -> name varchar(20), -> primary key(id)) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> desc userinfo2; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
第三种 修改
mysql> create table userinfo3( id int, name varchar(20)) engine = innodb default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> alter table userinfo3 modify id int auto_increment primary key ; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc userinfo3; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
第四种 联合主键
mysql> create table services( -> name varchar(20), -> host varchar(32), -> port int, -> primary key(host,port)) -> engine = innodb default charset=utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc services; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | host | varchar(32) | NO | PRI | | | | port | int(11) | NO | PRI | 0 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into services(name,host,port) values('nginx','1.1.1.1',80); Query OK, 1 row affected (0.00 sec) mysql> insert into services(name,host,port) values('nginx','1.1.1.1',90); Query OK, 1 row affected (0.00 sec) mysql> insert into services(name,host,port) values('nginx','1.1.1.1',90); ERROR 1062 (23000): Duplicate entry '1.1.1.1-90' for key 'PRIMARY'
4.2 auto_increment
约束字段为自动增长,被约束的字段必须同时被key约束
mysql> create table userinfo4( -> id int primary key auto_increment, -> name varchar(20)) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc userinfo4; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into userinfo4(name) values('wangys'); Query OK, 1 row affected (0.00 sec) mysql> select * from userinfo4; +----+--------+ | id | name | +----+--------+ | 1 | wangys | +----+--------+ 1 row in set (0.00 sec) # 也可以直接指定id值 mysql> insert into userinfo4(id,name) values(4,'wangys'); Query OK, 1 row affected (0.00 sec) mysql> select * from userinfo4; +----+--------+ | id | name | +----+--------+ | 1 | wangys | | 4 | wangys | +----+--------+
五、外键
- 被关联的字段必须有唯一约束,且为innodb表
# 创建一个部门表
mysql> create table departments( -> id int auto_increment primary key, -> name varchar(25) not null) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc departments; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) # 创建一个员工表dpt_id是外键,主表是部门表,关联id列 mysql> create table employee( -> id int auto_increment primary key, -> name varchar(25) not null, -> dpt_id int, -> foreign key(dpt_id) -> references departments(id) -> on delete cascade -> on update cascade) -> engine = innodb default charset = utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc employee; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | NO | | NULL | | | dpt_id | int(11) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+----------------+
# 删除主表某一行,则自动删除员工表关联的数据 mysql> select * from departments; +----+--------+ | id | name | +----+--------+ | 1 | 产品 | | 2 | 技术 | | 3 | 销售 | +----+--------+ 3 rows in set (0.00 sec) mysql> select * from employee; +----+--------+--------+ | id | name | dpt_id | +----+--------+--------+ | 1 | 老王 | 2 | | 3 | 老搜 | 3 | | 4 | 老郭 | 1 | +----+--------+--------+ 3 rows in set (0.00 sec) mysql> delete from departments where name ='产品'; Query OK, 1 row affected (0.00 sec) mysql> select * from departments; +----+--------+ | id | name | +----+--------+ | 2 | 技术 | | 3 | 销售 | +----+--------+ 2 rows in set (0.00 sec) mysql> select * from employee; +----+--------+--------+ | id | name | dpt_id | +----+--------+--------+ | 1 | 老王 | 2 | | 3 | 老搜 | 3 | +----+--------+--------+ 2 rows in set (0.00 sec)