十一.约束(一)

已于 2023-12-23 16:10:37 修改
阅读量903 收藏 17
点赞数 14
分类专栏： MySQL--基础 文章标签： 数据库 Mysql mysql
于 2023-12-23 14:56:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_71163619/article/details/135165879
版权

约束

1.约束(constraint)概念

1.1为什么需要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

  • 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
  • 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
  • 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

1.2什么是约束

约束是表级的强制规定。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

1.3约束的分类

  • 根据约束数据列的限制约束可分为：
    • 单列约束：每个约束只约束一列
    • 多列约束：每个约束可约束多列数据
  • 根据约束的作用范围，约束可分为：
    • 列级约束：只能作用在一个列上，跟在列的定义后面
    • 表级约束：可以作用在多个列上，不与列一起，而是单独定义
类型位置支持的约束类型是否可以起约束名
列级约束列的后面语法都支持，但外键没有效果不可以
表级约束所有列的下面默认和非空不支持，其他支持可以（主键没有效果）
  • 根据约束起的作用，约束可分为：
    • NOT NULL 非空约束，规定某个字段不能为空
    • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
    • PRIMARY KEY 主键(非空且唯一)约束
    • FOREIGN KEY 外键约束
    • CHECK 检查约束
    • DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

  • 查看某个表已有的约束
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints 
WHERE table_name = '表名称';

2.非空约束

2.1作用

限定某个字段/某列的值不允许为空
在这里插入图片描述

2.2关键字

NOT NULL

2.3特点

  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空

  • 一个表可以有很多列都分别限定了非空

  • 空字符串’'不等于NULL，0也不等于NULL

2.4添加非空约束

(1)建表时

CREATE TABLE 表名称(
	字段名  数据类型,
    字段名  数据类型 NOT NULL,  
    字段名  数据类型 NOT NULL
);

举例：

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);

CREATE TABLE student(
	sid int,
    sname varchar(20) not null,
    tel char(11) ,
    cardid char(18) not null
);

insert into student values(1,'张三','13710011002','110222198912032545'); #成功

insert into student values(2,'李四','13710011002',null);#身份证号为空
ERROR 1048 (23000): Column 'cardid' cannot be null

insert into student values(2,'李四',null,'110222198912032546');#成功，tel允许为空

insert into student values(3,null,null,'110222198912032547');#失败
ERROR 1048 (23000): Column 'sname' cannot be null

(2)建表后

alter table 表名称 modify 字段名 数据类型 not null;

举例：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

alter table student modify sname varchar(20) not null;

2.5删除非空约束

alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允许为空

或 

alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

举例：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3.唯一性约束

3.1作用

用来限制某个字段/某列的值不能重复
在这里插入图片描述

3.2关键字

UNIQUE

3.3特点

  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
  • MySQL会给唯一约束的列上默认创建一个唯一索引。

3.4添加唯一约束

(1)建表时

create table 表名称(
	字段名  数据类型,
    字段名  数据类型  unique,  
    字段名  数据类型  unique key,
    字段名  数据类型
);
create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [constraint 约束名] unique key(字段名)
);

举例：

create table student(
	sid int,
    sname varchar(20),
    tel char(11) unique,
    cardid char(18) unique key
);

CREATE TABLE t_course(
	cid INT UNIQUE,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);

CREATE TABLE USER(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 -- 使用表级约束语法
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
表示用户名和密码租户不能重复

insert into student values(1,'张三','13710011002','101223199012015623');
insert into student values(2,'李四','13710011003','101223199012015624');

mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+
|   1 | 张三  | 13710011002 | 101223199012015623 |
|   2 | 李四  | 13710011003 | 101223199012015624 |
+-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)

insert into student values(3,'王五','13710011004','101223199012015624'); #身份证号重复
ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid'

insert into student values(3,'王五','13710011003','101223199012015625'); 
ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel'

(2)建表后指定唯一键约束

字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

方式1：
alter table 表名称 add unique key(字段列表); 
方式2：
alter table 表名称 modify 字段名 字段类型 unique;

举例：

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

举例:

create table student(
	sid int primary key,
    sname varchar(20),
    tel char(11) ,
    cardid char(18) 
);

alter table student add unique key(tel);
alter table student add unique key(cardid);

3.5关于复合唯一约束

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

举例：

#学生表
create table student(
	sid int,	#学号
    sname varchar(20),			#姓名
    tel char(11) unique key,  #电话
    cardid char(18) unique key #身份证号
);

#课程表
create table course(
	cid int,  #课程编号
    cname varchar(20)     #课程名称
);

#选课表
create table student_course(
    id int,
	sid int,
    cid int,
    score int,
    unique key(sid,cid)  #复合唯一
);

insert into student values(1,'张三','13710011002','101223199012015623');#成功
insert into student values(2,'李四','13710011003','101223199012015624');#成功
insert into course values(1001,'Java'),(1002,'MySQL');#成功

mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+
|   1 | 张三  | 13710011002 | 101223199012015623 |
|   2 | 李四  | 13710011003 | 101223199012015624 |
+-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)

insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功

mysql> select * from student_course;
+----+------+------+-------+
| id | sid  | cid  | score |
+----+------+------+-------+
|  1 |    1 | 1001 |    89 |
|  2 |    1 | 1002 |    90 |
|  3 |    2 | 1001 |    88 |
|  4 |    2 | 1002 |    56 |
+----+------+------+-------+
4 rows in set (0.00 sec)

insert into student_course values (5, 1, 1001, 88);#失败

#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid'   违反sid-cid的复合唯一

3.5删除唯一约束

  • 添加唯一性约束的列上也会自动创建唯一索引。
  • 删除唯一约束只能通过删除唯一索引的方式删除。
  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些约束

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

注意：可以通过 show index from ‘表名称’; 查看表的索引

4.PRIMARY KEY 约束

4.1作用

用来唯一标识表中的一行记录

4.2关键字

primary key

4.3特点

  • 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
    在这里插入图片描述
  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  • MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
  • 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

4.4添加主键约束

(1)建表时指定主键约束

create table 表名称(
	字段名  数据类型  primary key, #列级模式
    字段名  数据类型,  
    字段名  数据类型  
);

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [constraint 约束名] primary key(字段名) #表级模式
);

举例：

create table temp(
	id int primary key,
    name varchar(20)
);

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

insert into temp values(1,'张三');#成功
insert into temp values(2,'李四');#成功

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

insert into temp values(1,'张三');#失败
ERROR 1062 (23000): Duplicate（重复） entry（键入，输入） '1' for key 'PRIMARY'

insert into temp values(1,'王五');#失败
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into temp values(3,'张三');#成功

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
|  3 | 张三 |
+----+------+
3 rows in set (0.00 sec)

insert into temp values(4,null);#成功

insert into temp values(null,'李琦');#失败
ERROR 1048 (23000): Column 'id' cannot be null

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

举例：

  • 列级约束
CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);
  • 表级约束
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

(2)建表后增加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

ALTER TABLE student ADD PRIMARY KEY (sid);

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

4.5关于复合主键

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名1,字段名2)  #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

#学生表
create table student(
	sid int primary key,  #学号
    sname varchar(20)     #学生姓名
);

#课程表
create table course(
	cid int primary key,  #课程编号
    cname varchar(20)     #课程名称
);

#选课表
create table student_course(
	sid int,
    cid int,
    score int,
    primary key(sid,cid)  #复合主键
);

insert into student values(1,'张三'),(2,'李四');
insert into course values(1001,'Java'),(1002,'MySQL');

mysql> select * from student;
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三  |
|   2 | 李四  |
+-----+-------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)

insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),(2,1002,56);

mysql> select * from student_course;
+-----+------+-------+
| sid | cid  | score |
+-----+------+-------+
|   1 | 1001 |    89 |
|   1 | 1002 |    90 |
|   2 | 1001 |    88 |
|   2 | 1002 |    56 |
+-----+------+-------+
4 rows in set (0.00 sec)

insert into student_course values(1, 1001, 100);
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'

举例：

CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);

4.6删除主键约束

alter table 表名称 drop primary key;

举例：

ALTER TABLE student DROP PRIMARY KEY;

ALTER TABLE emp5 DROP PRIMARY KEY;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

草莓田田圈~
关注
专栏目录
PostgreSQL的学习心得和知识总结（十一）|约束管理实现方案
孤傲小二~阿沐的博客
06-16 475
针对于PostgreSQL数据库而言，目前只支持约束（包括但不限于：主键、外键、唯一和检查约束）的创建、修改、删除和查看的功能。但是并没有支持以上四种约束的禁止和启用功能。（也即：Disable/Enable） 文章目录一、PostgreSQL数据库相关约束的使用及特性查看（结合系统表）主键约束外键约束唯一约束检查约束二、PostgreSQL数据库约束管理需求分析功能概述及场景分析三、PostgreSQL数据库禁止/启用约束功能具体设计方案检查（check）约束的具体功能实现主键和唯一约束的具体功能实现外键
MySQL数据库(四)DML(增.改.删).DDL(表创建.修改.删除.复制).约束.自增长列.事务.视图
白骨梦儿
08-19 513
一、insert 写法一： INSERT into 表名（列名1，……） VALUES(值1，……); 写法二： INSERT into 表名 SET 字段1=值1,字段2=值2,…… 方式一的优点： #优点： 1.支持插入多行 INSERT into 表名（列名1，……） VALUES(值1，……),(值11，……),(值111，……) 2.支持子查询 INSERT into 表名(列1，……) SELECT a,b……from …… 备注：子查询中的查询字段需要与insert插入字段对
十一、Xilinx FPGA相关约束，原语，函数使用及问题记录
weixin_41838250的博客
04-07 6055
约束，原语，函数，相关问题记录
运筹优化（十一）--无约束非线性规划
工作笔记
01-18 8499
这个系列将非线性规划是以“不是什么“定义的，也就是说，之前的线性规划模型使用连续决策变量，线性约束和线性目标函数，而非线性规划涵盖了所有其他单目标，连续决策变量的规划模型。意味着，非线性规划有许多不同的形式和算法。其中，有的有模型约束，有的则只有目标函数。在许多模型中，微积分能够得到可以利用的导数，有的则可能根本不存在导数。 无约束的非线性规划 在无约束优化中，线性规划的目标函数无界，而非线性...
全面风险管理相关术语和知识解读十一.pdf
10-04
管理层需考虑近期战术、中期战略和远期目标，以及相关的约束条件，确保风险对策符合整体业务方向。 2. **风险与收益的权衡**：在风险管理决策中，管理层必须平衡风险与收益的关系。投资者购买股票即承担公司风险，...
实验十一 综合实验.doc
12-27
实验十一是一个综合性的JavaWeb项目，旨在通过实践加深对JSP技术的理解，同时掌握一些进阶的开发技术和工具。在这个实验中，你需要构建一个学生信息管理系统，包括学生信息的注册、登录、增删改查等功能。以下是这个...
时序约束篇 Intel、XILINX 时序约束学习整理
02-19
时序约束是数字电路设计中的一个重要概念，涉及到时钟信号的生成、传输和控制。时序约束对数字电路的性能和可靠性产生着重要影响。本文将对时序约束的相关知识点进行详细的介绍和总结。 一、时序约束的基本概念 ...
技术转让合同（十一） .docx
12-25
违约责任条款规定了当任何一方违反合同时，应承担的法律责任和赔偿方式，起到约束和保障双方权益的作用。 十、争议的解决办法 当出现纠纷时，如何通过仲裁或诉讼等途径解决，这一条款提供了纠纷解决的路径。 十一...
CFA一级试题 十一Corporate Finance Corporate Investing and Financing Decisions_removed
04-29
为此，贷款通常会附加诸多限制性条款来约束公司的行为。 2. **激励机制**： - 管理者薪酬（Managerial compensation）是激励管理者追求股东最佳利益的主要手段。这通常包括年度绩效奖金和长期股票期权，以及基本...
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 698
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
秒杀业务: 简单梳理
m0_59925573的博客
07-23 1603
（1）在一人一单优惠券秒杀中，先是解决了因线程安全导致的超卖问题，为了满足一人一单的业务需求需要给查询订单表查询库存表上锁，但是synchronized只能满足单机环境下线程安全，在分布式环境对多个JVM需要使用分布式锁，于是手写分布式锁,但是在极端情况下，例如业务超时，会出现“误删”问题，手写锁无法解决，于是引入redisson,借助看门狗机制可以有效解决这个问题。
家政项目小程序的设计
2401_85476437的博客
07-26 335
管理员账户功能包括：系统首页，个人中心，用户管理，家政人员管理，家政服务管理，咨询信息管理，咨询服务管理，家政预约管理，留言板管理，系统管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，家政服务，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 245
图数据库查询与操作指南-以Nebula图数据库为例
AccessLog| 一款开源的日志分析系统
ClkLog的博客
07-26 325
系统采用Java语言、搭配OLAP数据库，涵盖基本web日志分析，同时提供性能分析，帮助高效运维
基于宝塔的 Bytebase 可视化安装、配置、部署教程
最新发布
Bytebase的博客
07-26 329
Bytebase是一款使用Go语言开发的开源数据库管理软件，旨在为DBA（数据库管理员）、开发人员和平台工程师提供一个基于Web的协作工作区，用于管理数据库的整个生命周期。本文将介绍如何在宝塔面板上可视化安装、部署、配置Bytebase教程。
redis常用指令
m0_62289159的博客
07-22 868
redis常用指令
SQL基础入门：解锁数据库管理的钥匙
WayneYalejk的博客
07-26 252
在数字化时代，数据已成为企业最宝贵的资产之一。为了高效地存储、查询和管理这些数据，SQL（Structured Query Language，结构化查询语言）应运而生。SQL不仅是数据库管理员的必备技能，也是数据分析师、开发人员等多个领域从业者的重要工具。本文将带您踏入SQL的世界，从基础概念到实际应用，一步步解锁数据库管理的钥匙。强调SQL在数据处理和分析中的重要性，鼓励读者通过实践不断深化对SQL的理解和应用。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1245
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
无约束优化与非线性方程数值方法详解
Schnabel的无约束优化和非线性方程数值方法》是一本经典的英文原版书籍，它被收录在应用数学经典系列中，旨在重新出版那些曾因印刷限制而暂时脱销的宝贵著作，以满足数学科学家持续的需求。本书共分十一章，内容...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值