SQL中的约束

2022-08-09 19:42:35

1、约束
---1.1概述
---1.2常见约束
---1.3常见约束示例
---1.4组合使用not null和unique
2、主键约束PK（primary key）
---2.1示例
---2.2主键相关术语
---2.3主键的作用
---2.4主键的分类
---2.5主键值自增
3、外键约束FK（foreign key）
---3.1示例
---3.2概述及相关术语
---3.3外键的分类
4、级联删除与级联更新
---4.1概述
---4.2级联删除
---4.3级联更新

约束

概述

1、什么是约束（constraint）？
实际上是对表中数据的限制条件。
2、设计表时加入约束的目的？
保证表中数据的完整和有效。

常见约束

非空约束（not null）：not null约束的字段不能为NULL值，必须赋具体数据。
唯一约束（unique）：unique约束的字段具有唯一性，不可重复。
主键约束（primary key）：约束的字段既不能为NULL，也不能重复(简称PK)。
外键约束（foreign key）：简称FR
检查约束（check）

常见约束示例
  • 非空约束
    示例：创建t_user表，name字段不能为空。
drop table if exists t_user;
create table t_user ( 
id int(10), 
name varchar(32) not null, 
email varchar(128)
);

不插入name：

insert into t_user
(id, email)
values(1, ‘123@qq.com‘);

会报错：

Error Code: 1364. 
Field ‘name‘ doesn‘t have a default value
  • 唯一约束
    1、示例一：创建t_user表，name不能为空，email保证唯一。
    方法一：列级约束
drop table if exists t_user;
create table t_user
(id int(10), 
name varchar(32) not null, 
email varchar(128) unique
);
insert into t_user
(id,name,email) 
values(1, ‘zhangsan‘, ‘111@qq.com‘);
insert into t_user
(id,name,email) 
values(2, ‘lisi‘, ‘111@qq.com‘);

SQL中的约束
只插入了zhangsan的，lisi字段重复了，报错：

Error Code: 1062. 
Duplicate entry ‘111@qq.com‘ for key ‘t_user.email‘

再插入空值：

insert into t_user
(id,name) 
values(3, ‘wangwu‘);
insert into t_user
(id,name) 
values(4, ‘zhaoliu‘);

SQL中的约束
结论：“unique约束”约束的字段不能重复，但是可以为NULL，NULL不是一个值，也不能用等号比较。
方法二：表级约束

drop table if exists t_user;
create table t_user
(id int(10), 
name varchar(32) not null, 
email varchar(128), 
unique(email)
);

与方法一效果上相同。
2、示例二：使用表级约束给多个字段联合添加约束（name和email两个字段联合唯一）。

drop table if exists t_user;
create table t_user
(id int(10), 
name varchar(32) not null, 
email varchar(128), 
unique(name, email)
);
insert into t_user
(id,name,email) 
values(1, ‘zhangsan‘, ‘111@qq.com‘), 
(2, ‘lisi‘, ‘111@qq.com‘);
insert into t_user
(id,name,email) 
values(2, ‘lisi‘, ‘111@qq.com‘);

SQL中的约束
由于表级联合约束限制，第二个lisi插入失败，Error Code还是1062。
3、表级约束还可以给约束起名字，以后可通过名字操作这个约束。

create table t_user
(id int(10), 
name varchar(32) not null, 
email varchar(128), 
constraint t_user_email_unique 
unique(email)
);

①查询唯一性约束的名字：show databases;
SQL中的约束
②查看information_schema库的表：
use information_schema;
show tables;
③TABLE_CONSTRAINTS该表格与门存储约束信息：
desc TABLE_CONSTRAINTS
④查询出表t_user中的唯一约束名称：

SELECT CONSTRAINT_NAME 
FROM TABLE_CONSTRAINTS
where TABLE_NAME = ‘t_user‘;

SQL中的约束

组合使用not null和unique
  • 作用：被not null和unique约束的字段，该字段即不能为null也不能重复。
  • 示例： 创建t_user表，用户编号为id，用户名称name即不能为空也不能重复。
drop table if exists t_user;
create table t_user
(id int(10), 
name varchar(32) not null unique
);

主键约束PK（primary key）

示例

1、创建表（插入数据代码省略）

create table t_user
(id int(10) primary key, 
name varchar(255), 
email varchar(255)
);

SQL中的约束
2、再次插入id为1的数据：

insert into t_user(id,name,email) 
values(1,‘ww‘,‘ww@123.com‘);

报错：

Error Code: 1062. 
Duplicate entry ‘1‘ for key ‘t_user.PRIMARY‘

3、插入id为空的数据：

insert into t_user(name,email) 
values(‘ww‘,‘ww@123.com‘);

报错：

Error Code: 1364. 
Field ‘id‘ doesn‘t have a default value
主键相关术语

1、主键约束：给某个字段添加的约束。
2、主键字段：表中某个字段添加主键约束之后，该字段被称为主键字段。
3、主键值：主键字段中出现的每一个数据都被称为主键值。

主键的作用

1、添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同。但本质是不同的，添加主键约束之后，主键不仅会有“not null unique”作用，而且主键字段还会自动添加“索引 — index”；
2、 一张表应该有主键，若没有，表示这张表是无效的（联想：数据库设计第一范式），“主键值”是当前行数据的唯一标识，“主键值”是当前行数据的身份证号。（即使表中两行数据完全相同，但是由于主键不同，我们也认为这是两行完全不同的数据）

主键的分类
  • 根据主键字段的字段数量来划分：
    1、单一主键（常用）：是给一个字段添加主键约束。
    例：（列级）单一主键约束：id int(10) primary key
    （表级）单一主键约束：id int(10), primary key(id)（更常用）
    2、复合主键：多个字段联合起来添加一个主键约束。（违背三范式，不建议使用）
    例：id int(10), name varchar(32), primary key(id, name)
  • 根据主键性质来划分：
    1、自然主键：主键值是一个自然数，并且这个自然数与业务没有任何关系。（推荐使用）
    2、业务主键：主键值和当前表中的业务紧密相关。例如：银行卡的卡号做主键、拿着身份证号码作为主键。（不推荐用，因为业务一旦发生改变，主键值可能也要随之变化，但又无法变化，其会导致主键值重复。）
  • 注：无论是单一主键还是复合主键，一张表中主键约束只能有一个。
主键值自增
  • MySQL中自动生成主键值（MySQL特有）
    1、定义：MySQL数据库管理系统中提供了一个自增数字auto_increment，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。自增数字默认从1开始，以1递增：1、2、3、4、…
    2、示例： 递增关键字：auto_increment
  • 示例
drop table if exists t_user;
create table t_user
(id int(32) primary key auto_increment, 
name varchar(32)
);
insert into t_user(name) 
values(‘zhangsan‘);
insert into t_user(name) 
values(‘lisi‘);

SQL中的约束
删除lisi的数据：

delete from t_user 
where id = 2;

再增加一个wangwu：

insert into t_user(name) 
value(‘wangwu‘);

SQL中的约束
（主键不会是2，而是继续在原有的基础上增加）

外键约束FK（foreign key）

示例

1、创建t_student表和t_class表（创建成一张表会造成数据的冗余）
t_class：
SQL中的约束
t_student：
SQL中的约束
2、t_student表要与t_class表有关系，需要在t_student表中添加一个classno字段做为外键。
t_student：
SQL中的约束
3、结论
classno值必须来自cno。
为了保证t_student表中的classno字段中的数据必须来自t_class表中的cno字段中数据，有必要给t_student表中classno字段添加外键约束；classno 称为外键字段，classno 中的100、200、
300称为外键值，classno在这里是单一外键。
4、相关SQL语句：

DROP TABLE
IF
	EXISTS t_student;
DROP TABLE
IF
	EXISTS t_class;
CREATE TABLE t_class ( cno INT ( 3 ) PRIMARY KEY, cname VARCHAR ( 128 ) NOT NULL UNIQUE );
CREATE TABLE t_student (
	sno INT ( 3 ) PRIMARY KEY,
	sname VARCHAR ( 32 ) NOT NULL,
	classno INT ( 3 ),
	CONSTRAINT t_student_classno_fk FOREIGN KEY ( classno ) REFERENCES t_class ( cno ) 
);
INSERT INTO t_class ( cno, cname )
VALUES
	( 100, ‘高三1班‘ );
INSERT INTO t_class ( cno, cname )
VALUES
	( 200, ‘高三2班‘ );
INSERT INTO t_class ( cno, cname )
VALUES
	( 300, ‘高三3班‘ );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 1, ‘lucy‘, 100 );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 2, ‘king‘, 100 );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 3, ‘lily‘, 200 );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 4, ‘ford‘, 200 );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 5, ‘allen‘, 300 );
INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 6, ‘teddy‘, 300 );

5、关键语句：
foreign key(classno) references t_class(cno)
6、测试：
在t_student表中插入一个班级编号为400的。

INSERT INTO t_student ( sno, sname, classno )
VALUES
	( 7, ‘Tom‘, 400 );

报错：

a foreign key constraint fails 
(`powernode`.`t_student`, 
CONSTRAINT `t_student_ibfk_1` 
FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

执行失败原因：
引用外键值在外键表t_class 中cno数据中不存在。

概述及相关术语
  • 概述
    1、外键在同一张表中可以有多个外键存在，外键字段可以为NULL，外键为空的数据也叫孤儿数据。
    2、被引用字段必须具有unique约束（不一定是主键）。
    3、有了外键引用之后，表分为父表和子表，以上父表：班级表 t_class； 子表是：学生表 t_student；创建表时先创建父表，再删除子表；插入数据时，先插入父表数据，再插入子表数据。
    4、在MySQL中没有提供修改外键约束的语法功能。
    5、重点：典型的一对多设计是在多的一方加外键。
  • 相关术语
    外键约束：给某个字段添加的外键约束。
    外键字段：被添加外键约束的字段。
    外键值：外键字段中的数据称为外键值。
外键的分类
  • 根据个数分类
    1、单一外键：给一个字段添加外键约束。
    2、复合外键：给多个字段联合添加一个外键。

级联删除与级联更新

概述
  • 用法
    在添加级联更新与级联删除的时候，需要在外键约束后面添关键字。
  • 注意
    级联更新与级联删除操作谨慎使用，因为级联操作会将数据改变或者删除（数据无价）。
级联删除
  • 定义及语法
    在删除父表数据的时候，级联删除子表中数据。
    语法：
    on delete cascade
  • 例子
    1、删除原有的外键约束（删除t_student中的外键）
    语法：
    ALTER TABLE 表名 DROP FOREIGN KEY 外键字段; 
ALTER TABLE t_student 
DROP FOREIGN KEY t_student_classno_fk;

2、添加外键约束及级联删除功能（增加t_student_classno_fk外键，并加入级联删除on delete cascade）
语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 引用表名 (引用表中字段名称) ON DELETE CASCADE;

ALTER TABLE t_student 
ADD CONSTRAINT t_student_classno_fk 
FOREIGN KEY (classno) REFERENCES t_class(cno) 
ON DELETE CASCADE;

3、测试：
删除t_class表中班级编号为300的信息，包括所在班级的学生信息

DELETE from t_class WHERE cno = 300;

t_class：
SQL中的约束
t_student：
SQL中的约束
删除父表数据之后，同时级联删除了子表中的数据。

级联更新
  • 定义与语法
    定义：在更新父表中数据的时候，级联更新子表中数据。
    语法：on update cascade
  • 例子
    1、删除外键约束（删除t_student中的外键）
    语法：
    ALTER TABLE 表名 DROP FOREIGN KEY 外键字段; 
alter table t_student 
drop foreign key t_student_classno_fk;

2、更新外键约束和添加级联更新功能（增t_student_classno_fk外键，并加入级联更新 on update cascade）
语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 引用表名称(引用表字段) ON UPDATE CASCADE;

ALTER TABLE t_student 
ADD CONSTRAINT t_student_classno_fk 
FOREIGN KEY (classno) REFERENCES t_class(cno) 
ON UPDATE CASCADE;

3、测试：
更新t_class表中班级编号200改为400，包括所在班级的学生信息。

UPDATE t_class SET cno = 400 
WHERE cno = 200;

t_class：
SQL中的约束
t_student：
SQL中的约束

SQL中的约束

上一篇：MySQL5.7解压版安装教程


下一篇：从零开始手写 mybatis （三）jdbc pool 从零实现数据库连接池