MySQL表的约束

文章目录

• 一、表的约束的意义
• 二、空属性(null/not null)
• 三、默认值(default)
• 四、列描述(comment)
• 五、zerofill
• 六、主键
• • 1.创建主键属性
  • 2.添加删除主键属性
  • 3.复合主键
• 七、自增长
• 八、唯一键
• 九、外键与外键约束
• • 1.外键
  • 2.外键约束

一、表的约束的意义

真正约束字段的是数据类型，但是数据类型约束单一，需要一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，比如有一个字段是email，要求是唯一的。
表的约束有很多，这里主要介绍下面几个：null/not null,default,comment,zerofill,primary key,auto_increment,unique key。

二、空属性(null/not null)

数据库默认字段基本都是空，但是实际开发的时候，尽可能保证字段不为空，因为数据为空没有办法参与运算。

注意，在C中，NULL表示的是一个宏，值为0，但是在MySQL中NULL表示的就是空，是什么都没有。

mysql> create table t1(
    -> name varchar(16),
    -> age int
    -> );
mysql> insert into t1 (name) values('张三');

当我们建立一个表没有指定是否为空的时候，有些数据是可以为空的。此时我们只插入name而不插入age，可以查看一下插入的结果：

如果我们约束用户必须插入值，而不能为空的时候，就可以在类型后面加上not null：

mysql> create table t2(
    -> name varchar(16) not null,
    -> age int not null
    -> );
mysql> insert into t2 (name) values('张三');//错误，必须插入age

对比t1和t2表的结构，发现NULL字段中已经明确了是否为空。

三、默认值(default)

default表示的是若为空，则值为什么。当与not null连用的时候，保留default属性。

mysql> create table t3(
    -> name varchar(60) not null,
    -> age int default 18,//为空则为18
    -> sex char(1) not null default '男'
    -> );
mysql> insert into t3 (name) values('张三');//成功

虽然sex不能为空，但是有default保留了default的属性，也满足了不为空的条件，因此可以插入成功。
我们可以查看一下表内容：

四、列描述(comment)

comment没有实际意义，专门用来描述字段，会根据创建语句进行保存，用来给程序员或者DBA来进行了解。

mysql> create table t7(
    -> name varchar(60) not null,
    -> age tinyint unsigned default 0 comment "年龄",
    -> sex char(2) default "男" comment "性别"
    -> );

此时我们使用desc查看表的属性是看不到comment的内容的，需要使用show：

mysql> show create table t7\G;

五、zerofill

在我们查看表的结构的时候，会发现int后面括号是11，这个是什么含义呢？
如果没有zerofill这个数字是没有任何意义的。

mysql> create table t4(
    -> a int(5) unsigned zerofill
    -> );
mysql> insert into t4 (a) values(2);

此时我们再向其中插入数据，会发现在不够5位的时候会使用0来进行补位。

即如果宽度小于设定的宽度，自动填充0，要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1，我们可以使用hex来证明：

六、主键

1.创建主键属性

一张表中只能由一个主键。
数据库存入数据并不是目的，也要方便进行提取，一般而言，要求数据库中记录有一个特定的唯一标识。
即，所有行中该列数据不能有重复的。并且主键不能为空。

mysql> create table t5(
    -> id int unsigned primary key comment '主键',
    -> name varchar(16) not null
    -> );

使用primary key可以添加主键此时查看表的结构可以发现：

key这一列中id为PRI表示的就是主键的意思。

mysql> insert into t5 (id,name) values(10,'张三');
mysql> insert into t5 (id,name) values(11,'张三');//成功，只要主键不重复就可以
mysql> insert into t5 (id,name) values(10,'李四');//失败，主键重复了

2.添加删除主键属性

mysql> alter table t5 drop primary key;//删除主键属性
mysql> alter table t5 add primary key(id);//添加主键属性

注意，在添加主键的时候要保证被添加的列没有重复数据，如果有重复数据的话就会添加失败。

3.复合主键

复合主键，即多列信息合起来作为表的主键。只有两列数据都有冲突的时候才会发生错误。

mysql> create table t6(
    -> id int unsigned,
    -> course char(10) comment '课的编号',
    -> socre tinyint unsigned default 60 comment '这个是默认的得分',
    -> primary key(id,course)//将id和course设为复合主键
    -> );
mysql> insert into t6 (id,course) values(1,'ss');
mysql> insert into t6 (id,course) values(1,'aa');//成功，有一个冲突可以进行插入
mysql> insert into t6 (id,course) values(2,'aa');//成功
mysql> insert into t6 (id,course) values(2,'aa');//失败，两个都冲突不能进行插入

七、自增长

auto_increment：对应的字段，不给值会被系统自动触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值，只能赋给不能进行修改的值，作为逻辑主键。
自增长的特点：

• 任何字段要做自增长，前提是本身是一个索引。(key一栏有值)
• 自增长字段必须是整数。
• 一张表最多只能有一个自增长。

目前我们学习到的不能修改的值只有主键，因此暂时自增长只能给主键使用。

mysql> create table t8(
    -> id int unsigned primary key auto_increment comment '是一个自增长',
    -> name varchar(20) default ''
    -> );

这里需要注意，default的是一个空串，不是NULL!，两者是有区别的。

mysql> insert into t8 (name) values('a');//没有带id但是插入成功了
mysql> insert into t8 (name) values('b');
mysql> insert into t8 (name) values('c');
mysql> insert into t8 (name) values('d');

此时再查看表中的数据，可以看到：

id被自动从1开始赋值，并且每增加一行，它的值就增加1。称为自增长。
那么如果指定id呢？会正常插入。

mysql> insert into t8 (id,name) values(1000,'e');
mysql> insert into t8 (name) values('f');

但是如果插入之后不再指定id，那么自增长将会从最大的数开始：

八、唯一键

一张表中有往往多个字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就能解决表中的很多字段需要唯一性约束的问题。唯一键的本质和主键其实差不多，唯一键允许为空，而且可以多个为空，空字段不参与唯一键比较。
主键和唯一键并不冲突，在一张表中可以有一个主键，也可以同时具有唯一键。
主键不是设置了主键才具有主键的属性的，而是对应的属性被选择作为了主键，那么如果没有被选择但仍然需要唯一属性的列怎么办呢？因此引入了唯一键。
创建唯一键使用的是unique。

mysql> create table t9(
    -> id int unsigned unique comment '唯一键',
    -> name char(20) not null
    -> );

可以看到key的字段是UNI表示的就是一个唯一键。

mysql> insert into t9 (id,name) values(10,'a');//成功
mysql> insert into t9 (id,name) values(10,'b');//失败，唯一键冲突了
mysql> insert into t9 (id,name) values('c');//成功，不插入唯一键默认为空
mysql> insert into t9 (id,name) values('d');//成功，虽然有空了，但是空不参与比较，不算重复

唯一键在主键不存在，且唯一键非空的情况下，会变成主键：

mysql> delete from t9 where id<=>NULL;//删除唯一键中的空
mysql> alter table t9 modify id int unsigned not null unique;//将唯一键设为非空

此时我们再来查看表的属性：

会发现唯一键的位置变成了PRI。但如果表中原本就有主键了，唯一键还是唯一键，只不过不能为空。

九、外键与外键约束

1.外键

以上的内容都是针对一张表来说的，外键是让多张表关联起来的一种属性。
一般而言，一张表中的主键通常为另一张表中的外键。
比如说，这里有两张表，分别为学生表和班级表：

id	name	class_id
100	张三	10
101	李四	20

class_id	name
10	十八路谭腿班
20	洪家铁线拳班
其中学生表的最后一个属性为class_id，对应的是班级的第一列class_id。在这里，班级表的主键为class_id，作为学生表的外键。
通过class_id这两张表就可以连接起来了。
但是这只是在语义上连接起来了，在表的操作上还是需要我们来进行链接的。通过需求分析，大致分析出应该规定：

1.学生表中不能插入不存在的班级class_id。
2.班级表在进行删除的时候，如果班级里有学生是不能进行删除的。

2.外键约束

mysql引入外键约束的语法，来实现以上规定。
通常是在一个表中将外键关联其他表中的主键：
foreign key(class_id) references class class(id)

mysql> create table class(
    -> id int primary key,
    -> class_name varchar(20) not null
    -> );//创建班级表
mysql> create table str(
    -> id int primary key,
    -> name varchar(20) not null,
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> );//创建学生表，关联班级表

此时我们再向学生表中进行插入，或者删除班级表就会受到约束了：

mysql> insert into class (id,class_name) values(1,'光照会');
mysql> insert into class (id,class_name) values(2,'复仇者联盟');//首先插入两个班级id
mysql> insert into str (id,name,class_id) values(1,'ironman',1);//向学生表中插入1班学生
mysql> insert into str (id,name,class_id) values(3,'黑蝠王',1);//向学生表中插入1班学生
mysql> insert into str (id,name,class_id) values(2,'spiderman',2);//向学生表中插入2班学生
mysql> insert into str (id,name,class_id) values(3,'superman',3);//出错，class表中没有3班
mysql> delete from class where id=1;//删除失败，因为id为1关联了str中的行，即班级中有人
mysql> delete from str where class_id=1;//将str中class_id为1的人删掉
mysql> delete from class where id=1;//此时就可以将id为1的班级删除了

外键是用来实现表与表关系的字段，其实是我们自己臆想出来的，但是只要是人来操作就会发生错误，为了强约束表之间的关系，才引入了外键约束的语法，从而mysql才能更好的帮助来维护表之间的逻辑关系。