MySQL表的约束(基础篇)

目录

约束类型

自增主键<--->分布式

引出小问题

约束类型

约束类型都是根据这个表格来叙述的:

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• NOT NULL

NULL :->指定某列可不可以为null ,约束类型 NOT NULL

• 表中NULL 为yes 代表选填项 可以为空
• 表中NULL 为 no 代表必填项 不能为空

没有约束情况下 ->指定列可以为NULL-->可以插入NULL

create table student(id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student values(NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| NULL | NULL |
+------+------+

•  有约束的情况下

mysql> create table student(id int not null,name varchar(20) not null);//约束
Query OK, 0 rows affected (0.03 sec)

 desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

insert into student values(NULL,NULL);
ERROR 1048 (23000): Column 'id' cannot be null

此时表格中NULL变为了NO ,也就是必填项,当我们再次插入NULL,就会失败因为进行了约束.

• UNIQUE 

unique 保证某列的每一行不能重复,比如当你插入 id =1 是再次插入1就会插入失败

• 无约束情况:

create table student(id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    1 | 张三   |
|    1 | 张三   |
|    1 | 张三   |
+------+--------+
4 rows in set (0.00 sec)

• 有约束情况:

create table student(id int unique,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

当你再次插入重复元素的时候 他就会报错 Duplicate entry '1' for key 'id' ,意思是插入的id重复,因为将 id 进行了约束 此时表格key 中也进行了改变原先什么都没有,这时有UNI的标记,这就是不能进行重复的意思.

• DEFAULT

default 是默认值 的意思 ,如果不进行约束的情况下默认的默认值就是NULL.什么时候会显示默认值呢?? 当我们指定列插入的时候,比如向这个表格 id 和name你只插入了一个值,另外的一个值就是为NULL.

无约束情况:

create table student(id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into student(id) values(1);-- 指定id列插入
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

我们可以发现无约束情况默认的默认值就是NULL;

• 有约束情况

mysql> create table student(id int default 0,name varchar(20) default '无名氏');
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| id    | int(11)     | YES  |     | 0         |       |
| name  | varchar(20) | YES  |     | 无名氏    |       |
+-------+-------------+------+-----+-----------+-------+
2 rows in set (0.00 sec)

mysql> select * from student;
Empty set (0.01 sec)

mysql> insert into student(id) values(9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-----------+
| id   | name      |
+------+-----------+
|    9 | 无名氏    |
+------+-----------+
1 row in set (0.00 sec)

当我们加了default约束的情况下,表格DEFAULT的那一列就显示了我输入的默认值,当我们指定列插入时,只插入了id,没有插入name,所以name那一列就输出默认值.

• primary key ->主键

这个是NOTNULL 和UNIQUE的结合 .

• 插入的值不能为NULL
• 插入的值不能重复
• 一个表里只能有一个主键

插入的值不能为NULL:

create table student(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql>

如果插入的值为NULL,并且这一列被主键约束,那么就会插入失败

主键不能重复的情况:

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当我们再次插入重复的值的时候并且这一列被主键约束,那么不能插入->插入失败;

一个表中只能有一个主键

mysql> create table student (id int primary key,name varchar(20) primary key);
ERROR 1068 (42000): Multiple primary key defined

当我们有两个主键他也会报错

• 自增主键 -> primary key auto_increment

为什么会有自增主键??

当我们有了主键:主键是不能重复,不能为null,一个表中不能有多个主键,这样我们每次在插入元素的时候,都要确保我上一次插入的是否为null,是否是重复的,这样做会对程序猿不太友好,mysql就有了自增主键.

自增主键:能够保证不为null并且不重复.--->当然只针对整形 ,其他类型无法+1操作

当我们将id设置为自增主键的时候,我们插入的值可以为NULL,这样id就会从1开始增加,保证不重复,当我们手动id后,再次插入id为null,他就会根据当前表中id最大值继续自增.

mysql> create table student (id int primary key auto_increment ,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| 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> insert into student values(NULL,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
|  3 | 张三   |
|  4 | 张三   |
|  5 | 张三   |
+----+--------+
5 rows in set (0.00 sec)

当我们手动id后,在插入NULL;

mysql> insert into student values(50,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(NULL,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
|  3 | 张三   |
|  4 | 张三   |
|  5 | 张三   |
| 50 | 李四   |
| 51 | 李四   |
| 52 | 李四   |
| 53 | 李四   |
| 54 | 李四   |
| 55 | 李四   |
+----+--------+
11 rows in set (0.00 sec)

自增主键<--->分布式

自增主键用于分布式好不好呢???

先说一下什么是分布式:

当数据量特别大的时候,我们就需要多台机器一起运行,这就是分布式.

 比如上图就是一个mysql集群,需要三个mysql服务器一起运行.

再次说明一下自增主键是干什么的???  自增主键就是保证生成唯一id并且保证不重复,对于mysql一个服务器来说自增到某一个值来说他就知道这个最大值是什么.

但是对于分布式来说:在使用自增主键时,每一个mysql服务器都会自增到某一个最大值,对于mysql服务器本身是知道自己的最大值是多少的,但是在这三个MySQL服务器之间是不知道彼此的最大值是多少,这就导致有可能重复,这自增主键就对分布式不咋好使了.

那怎样才能让分布式上获取id唯一,并且不能重复呢??

可以采取 这个公式 唯一id = 时间戳 + 主机编号/机房编号 + 随机因子

这样就可以在大概率的情况生成唯一id并且不重复.

为什么呢????

因为在不同的时间戳生成的id肯定不一样,当时间戳相同时有拼接上主机编号保证其id不一样,又添加了随机因子 -->这样生成的id大概率是不一样的.

但是,概率肯定不能100%,但是对于庞大工程来说,极低的概率生成id重复 可以进行忽略.

• 外键约束

外键约束就是让两个表之间产生联系 ;一个表使用外键让其与另一个表的主键产生联系;

语法 : create tavble 表名 ...字段.. 与其他表相关联的字段 , foreign key (与其他表相关联的字段) references 其他表(与这个表相关联的字段);

举一个栗子:

比如:我们一个班级有很多学生,但是当一个不是这个班级学生,就不能让这个学生来这个班级上课,他应该去对应的班级上课,此时我们怎么知道他不是这个班级的.我们有一个班级表和一个学生表,班级表记录着学生的信息和学号,还有一个学生表,这个学生表就记录着学生名字,和对应班级编号,此时学生表对应的班级编号和班级表对应着学生的信息就产生了联系.

我们将约束的一方为父表(班级表约束着其他同学不能来这个班级)

我们将被约束的一方为字表(学生表为被班级表进行约束)

此时,在数据库里面我们就能利用外键来让学生表和班级表产生关系.

mysql> create table class(classId int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> create table student(studentId int primary key auto_increment,name varchar(20),classId int,foreign key(classId) references class(classId));
Query OK, 0 rows affected (0.03 sec)

mysql> desc class;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| classId | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc student;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| studentId | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  |     | NULL    |                |
| classId   | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

• 第一种情况

mysql> insert into student values(1,'张三',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

当班级表为空时,往学生表里插入,就会报错... 因为班级表里面还没有创建班级就不能存放学生信息

• 第二种情况

mysql> insert into student values(32,'张三',8);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

当学生的班级id没有在班级表中创建,插入这个学生...就会报错,因为没有这个班级.

• 第三种情况

mysql> insert into student values(98,'李四',1);-- 往classId 1 中插入学生
Query OK, 1 row affected (0.00 sec)

mysql> delete from class where classId = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
mysql>

当我们往classId 1号插入学生,在删除classId 就不能进行删除,因为这个班级里还有学生.

此时得出结论:

• 父表对子表产生限制的同时,子表也会对父表产生限制,父表对子表的限制是不能随意的让子表进行插入和修改,子表对父表的限制是不能随意的删除和修改
• 当往子表插入时,同时也会触发父表查询
• 当触发父表查询时可能会触发索引就会让查询速度变快

当建立外键约束时:

mysql要求,父表与子表相关联的字段,必须被主键或者unique约束,就会触发索引让其速度更快,因为主键,unique自带索引.

引出小问题

我们在平常购物时都会有一个订单表 和 一个商品表

商品表(goodsId,name,price);

订单表(name,goodsId)

商品表和订单表通过商品id相关联 这里的商品表就是父表,订单表就是子表.

我们经常会发现一个问题:当我们上一次点开商品的时候想着下一次要买,当我们下一次再去点开商品要准备付款的时候,就显示商品已经下单.

既然父表与子表通过goodsId相关联 受着外键约束,怎么才外键约束的情况下,将父表删除??

我们上面讲过在父表里面还有子表的数据时,会受着外键所约束无法删除.

思路:

我们知道操作系统删除时并不是真正的删除,而是将每一个盘块标记成无效,这里也是一样的,我们可以在父表里增加一个字段 ,这个字段用来表示商品下没下架,当标记为1时,代表商品没有下架那就正常显示,当标记为0时,代表商品下架 就不显示商品 -->其实这里没有将父表删除.