目录
一、约束概述
约束用于确保数据库的数据满足特定的商业规则。MySQL中的常见约束有NOT NULL(非空), PRIMARY KEY(主键), FOREIGN KEY(外键), UNIQUE(唯一)和CHECK(检查)五种。
二、 PRIMARY KEY(主键)
1.作用 :
主键用于唯一的标示表中的数据,当对表中某个字段定义主键约束后,该列的数据不允许重复。在实际开发中,一般情况下每个表都会定义一个主键约束。
2.格式 :
方式一 :
字段名 字段类型 PRIMARY KEY
方式二 :
PRIMARY KEY(字段名)
注意事项——
1° 定义主键约束的字段不允许重复,且不允许为空。
2° 一张表最多只能有一个主键,但可以定义一个复合主键,格式为在定义表的字段的最后,加上一行"PRIMARY KEY(column_1, column_2...)",定义复合主键后,要求向表添加数据时,复合主键约束的字段不能完全相同,即允许有不完全相同。
3° 使用DESC table_name可以快速查看表的主键。
3.自增长 :
定义自增长是为了使表中的某个整型字段(eg : id)能够从1开始自动的增长。
定义格式如下 :
字段名 整型 PRIMARY KEY AUTO_INCREMENT
使用格式如下 :
①INSERT INTO table_name(column1,column2...) VALUES(NULL, val2...);
②INSERT INTO table_name(column2,column3...) VALUES(val2,val3...);
③INSERT INTO table_name VALUES(NULL, val2...);
PS :
1>传入NULL给定义了自增长的字段,实际会自动将1,2,3,4.....传给该字段。
2>一般地,自增长都会和PRIMARY KEY 一起配合使用,但也可以单独使用(需要UNIQUE约束的配合)。3>一般地,自增长修饰的字段均为整型;实际浮点型也可以,但是使用频率非常低。
4>自增长默认从1开始,可以通过ALTER TABLE table_name AUTO_INCREMENT = ___; 语句来修改。
5>若向自增长修饰的字段传入了一个指定值,则以指定值为准进行自增长。并且,一般地,若程序员手动指定了自增长的默认值,就按照指定后自增长的规则来添加数据。(要专一)
4.演示 :
演示Ⅰ—— 主键约束的特点
先来创建一张水果表,并定义水果编号字段fno为主键,代码如下 :
CREATETABLE IF NOT EXISTS `fruit`( `fno` MEDIUMINT PRIMARY KEY, `fname` VARCHAR(64)NOTNULL DEFAULT '', `fswe` TINYINTNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `fruit` VALUES(1,'apple',13),(2,'watermelon',12),(3,'grape',17);SELECT*FROM fruit;
水果表如下 :
编辑
尝试向水果表中添加"fno = 2,但fname和fswe均与表中已存在记录不同"的数据,如下 :
INSERTINTO `fruit` VALUES(2,'pear',10);
这时Navicat会报错,提示主键不允许重复,如下图所示 :
编辑
继续,尝试向水果表中添加fno = 4,但fname和fswe均与表中已存在记录相同的数据,如下 :
INSERTINTO `fruit` VALUES(4,'grape',17);SELECT*FROM fruit;
编辑
继续,尝试向水果表中添加fno = NULL的数据,如下 :
INSERTINTO `fruit` VALUES(NULL,'fruit',999);
Navicat会报错,提示fno不为空,如下 :
编辑
可以使用DESC fruit来查看水果表的接口,如下 :
编辑
可以看到fno字段做了PRI(主键)的标记。
演示Ⅱ—— 复合主键
定义一个新的水果表fruit_2,并为其字段fno和fname设置复合主键约束,代码如下 :
CREATETABLE IF NOT EXISTS `fruit_2`( `fno` MEDIUMINT, `fname` VARCHAR(64), `fcolor` VARCHAR(32)NOTNULL DEFAULT '', PRIMARY KEY(fno,fname)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `fruit_2` VALUES(1,'watermelon','green'),(2,'apple','red'),(3,'grape','purple');SELECT*FROM fruit_2;
fruit_2水果表如下:
编辑
尝试向fruit_2水果表中添加fno = 3,fname = 'grape_2'的数据,如下 :
INSERTINTO `fruit_2` VALUES(3,'grape_2','purple');SELECT*FROM fruit_2;
编辑
尝试向水果表fruit_2中添加fno = 3, fname = 'grape', fcolor = 'blue'的数据,如下 :
INSERTINTO `fruit_2` VALUES(3,'grape','blue');
编辑
同样提示主键 '3-grape' 不能重复。
继续,使用DESC查看fruit_2的表结构,如下 :
编辑
演示Ⅲ—— 自增长
先来建一张学生表,指定id为主键并且设置自增长,代码如下 :
CREATETABLE IF NOT EXISTS `student_demo`( `id` MEDIUMINTUNSIGNED PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255)NOTNULL DEFAULT '', `sex` CHAR(32) CHECK(sex IN('male','female'))) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
向学生表中添加数据,代码如下 :
INSERTINTO `student_demo`(id,`name`,sex)VALUES(NULL,'Cyan','male'),(NULL,'Ice','male');INSERTINTO `student_demo`(`name`,sex)VALUES('Rain','female'),('Five','female');ALTERTABLE `student_demo` AUTO_INCREMENT =100;INSERTINTO `student_demo` VALUES(NULL,'Kyrie','male'),(NULL,'James','male');SELECT*FROM student_demo;
自增长效果如下 :
编辑
三、UNIQUE(唯一)
1.作用 :
当某个字段定义了UNIQUE约束后,表示该列数据的值是唯一的,不能重复。
2.格式 :
字段名 字段类型 UNIQUE
注意事项——
1° 对于UNIQUE约束的字段,若没有定义NOT NULL约束,则该字段是允许有多个NULL的。
2°一张表可以有多个UNIQUE约束的字段。
3° 仍然可以在DESC显示出的表结构中,查看表中约束条件的情况。
3.演示 :
先来建一张水果表fruit_3,玩儿得花一点,对表中所有字段都添加UNIQUE约束,代码如下 :
CREATETABLE IF NOT EXISTS `fruit_3`( `fno` MEDIUMINT UNIQUE NOTNULL, `fname` VARCHAR(64) UNIQUE, `fswe` TINYINT UNIQUE, `fcol` VARCHAR(32) UNIQUE ) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `fruit_3` VALUES(1,'apple',13,'red'),(2,'pear',10,'yellow'),(3,'watermelon',15,'green');SELECT*FROM `fruit_3`;
编辑
尝试向表中添加fno = 4,fname = 'pear', fswe = 11, fcol = NULL的数据,如下 :
INSERTINTO `fruit_3` VALUES(4,'pear',11,NULL);
编辑
可以看到,Navicat会报错,并提示fname字段有重复内容。
继续,尝试向表中添加多个fcol = NULL的数据,如下 :
INSERTINTO `fruit_3` VALUES(4,'grape',16,NULL),(5,'grape_2',17,NULL),(6,'strawberry',18,NULL);SELECT*FROM `fruit_3`;
编辑
同样,可以使用DESC查看fruit_3表的结构,如下图所示 :
编辑
可以看到,同时定义了UNIQUE与NOT NULL约束的字段,就相当于一个主键约束的字段。
四、FOREIGN(外键)
1.作用 :
外键用于定义主表和从表之间的关系。外键约束要定义在从表上;主表则必须具有PRIMARY KEY约束或UNIQUE约束。
当从表中的字段定义了外键约束后,要求该字段的数据必须在主表的主键列存在或者为NULL(注意此处指的是从表中的数据为NULL,且前提是主表对应的字段允许为空),否则无法向从表中添加数据;并且,如果想删除主表主键列的某条数据,必须要求从表中没有相应的数据与之关联,即在外键约束下,数据无法任意删除。
2.格式 :
FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或UNIQUE字段名)
注意事项——
①外键关系要在创建表时单独定义,类似于单独定义主键的方式,写在字段的最后。②INNODB类型的表才支持外键
③外键约束的字段类型要和对应主键的字段类型一致(长度可以不同)
④若一张从表中要定义多个外键(即与多张主表发生关联), 则不同的外键要单独定义。
3.演示 :
建立如下的主表与从表关系 : (主表为班级表classes,从表为学生表stus)
编辑
代码如下 :
# 创建主表(先创建主表) CREATETABLE IF NOT EXISTS `classes`( `c_id` MEDIUMINT PRIMARY KEY, `c_name` VARCHAR(32)NOTNULL DEFAULT '', `c_tuition` DECIMAL(8,2)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;# 创建从表 CREATETABLE IF NOT EXISTS `stus`( `s_id` MEDIUMINT UNIQUE NOTNULL, `s_name` VARCHAR(64)NOTNULL DEFAULT '', `s_classId` MEDIUMINT, FOREIGN KEY (s_classId) REFERENCES classes(c_id)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
先向主表中添加数据,如下 :
INSERTINTO `classes` VALUES(55,'Java',20000.0),(76,'C++',15000.0),(141,'Python',12000.0);SELECT*FROM classes;
编辑
尝试向从表中添加s_classId字段在主表中不存在的数据,如下 :
INSERTINTO `stus` VALUES(1,'Cyan',50),(2,'Five',75),(3,'Rain',140);
编辑
显然不成功。将s_classId改为在主表中c_id字段存在的值,再次尝试添加,如下 :
INSERTINTO `stus` VALUES(1,'Cyan',55),(2,'Five',76),(3,'Rain',141);SELECT*FROM `stus`;
编辑
尝试删除主表中的某条记录,由于此时在从表中定义了外键约束,并且已经有相关记录与之关联,所以一定会删除失败,如下 :
DELETEFROM `classes` WHERE c_id =141;
编辑
删除从表中与主表主键某个数据相关联的全部记录,便可以删除主表中主键的该数据。如下 :
DELETEFROM `stus` WHERE s_classId =141;DELETEFROM `classes` WHERE c_id =141;SELECT*FROM classes;
编辑
五、CHECK(检查)
1.作用 :
CHECK用于强制限制行数据必须满足的条件。若某个字段使用CHECK约束进行了修饰,那么向表中添加记录时,要求这一具体的字段必须满足CHECK约束的条件,否则报错。
PS :
Oracle 和 SQL Server均支持CHECK,MySQL8.0开始也支持CHECK(MySQL5.7版本仅支持CHECK的语法校验,但并不会实际生效)。
2.格式 :
字段名 字段类型 CHECK(check_expression)
注意事项——
①在MySQL中实现CHECK的功能,一般是在程序中控制,或者通过触发器来完成。
3.演示 :
先来建一张水果表,要求水果名必须带grape,并且要求水果的甜度必须大于等于10。代码如下 :
CREATETABLE IF NOT EXISTS `fruit_EX`( `f_id` MEDIUMINT PRIMARY KEY, `f_name` VARCHAR(64) CHECK(f_name LIKE'%grape%'), `f_swe` TINYINTNOTNULL DEFAULT 10 CHECK(f_swe >=10)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
尝试向表中添加不符合CHECK约束的记录,如下 :
INSERTINTO `fruit_EX`(f_id, f_name, f_swe)VALUES(1,'red_grape',9),(2,'green_grape',8),(3,'pueple_grape',7),(4,'violet_grape',6),(5,'what?',11);
Navicat会报错,提示不满足CHECK约束条件,如下 :
编辑
将其修改为复合CHECK约束条件的记录,并再次尝试添加,如下 :
INSERTINTO `fruit_EX`(f_id, f_name, f_swe)VALUES(1,'red_grape',19),(2,'green_grape',18),(3,'pueple_grape',17),(4,'violet_grape',16),(5,'grape_grape',11);SELECT*FROM fruit_EX;
编辑
System.out.println("END------------------------------------------------------------------------------");