文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

『 MySQL篇 』:MySQL表的相关约束

2023-08-16 21:29

关注

文章目录

)
约束描述关键字
非空约束限制该字段的数据不能为 nullNOT NULL
唯一性约束保证该字段的所有数据都是唯一. 不重复的UNIQUE
主键约束主键是一行数据唯一的表示 , 要求非空且唯一PRIMARY KEY
默认约束保存数据时 ,如果未指定该字段的值 , 则采用默认值DEFAULT
检查约束(8.0.16版本之后)保证字段值满足某一个条件CHECK
外键约束用来让两张表的数据之间建立连接 , 保证数据的一致性和完整性FOREIGN KEY
1 . 非空约束 (not null)

创建表时进行使用 , 指定某一列的值不为空

create table 表名(列名 类型 not null);

创建表中包含 字段名为 name 字段类型为 varchar(10) , name 不允许为空值

mysql> create table user(name varchar(10) not null,gender char(1));Query OK, 0 rows affected (0.03 sec)mysql> desc user;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| name   | varchar(10) | NO   |     | NULL    |       || gender | char(1)     | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)-- 插入姓名为空值时mysql> insert into user values(null,'男');ERROR 1048 (23000): Column 'name' cannot be nullmysql> insert into user values ('张三','男');Query OK, 1 row affected (0.00 sec)
2 . 唯一性约束(unique)

在创建表时 , 对某一列使用唯一性约束 , 则该列的值不能重复 .

create table 表名(列名 类型 unique);

创建表中包含字段名为 name , 字段类型为 varchar(10) , name 不允许为空值 . 且name 为唯一

mysql> create table user(name varchar(20)not null unique);  -- name 列不能重复且非空Query OK, 0 rows affected (0.05 sec)mysql> desc user;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(20) | NO   | PRI | NULL    |       |+-------+-------------+------+-----+---------+-------+1 row in set (0.00 sec)#第一次直接插入mysql> insert into user values('张三');Query OK, 1 row affected (0.00 sec)#当插入重复值时 , 已存在不能重复添加mysql> insert into user values('张三');ERROR 1062 (23000): Duplicate entry '张三' for key 'name'mysql>

如果没有指定非空 , 那么unique 字段可以有多个 null , 一张表中可以有多个unique 字段

# 修改user表中的约束alter table user modify age int not null unique;
3 . check约束

用于强制行数据必须满足的条件 .

4 . 默认约束(default)

在创建表的时候可以使用 , 可以设置列的默认值

create table 表名(列名 类型 default 默认值..);

创建表中包含字段名为sex , 字段类型为char( 1 ) , 约束条件为 默认值为 ‘男’

mysql> create table user(id int , gender char(1) default '男');Query OK, 0 rows affected (0.01 sec)mysql> desc user;+--------+---------+------+-----+---------+-------+| Field  | Type    | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| id     | int(11) | YES  |     | NULL    |       || gender | char(1) | YES  |     | 男      |       |+--------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)
5 . 主键约束(primary key)

主键与外键之间的区别 ?

主键(主码) :主键用于唯一标识一个元组,不能有重复,不允许为空。一个表只能有一个主键。

外键(外码) :外键用来和其他表建立联系用,外键是另一表的主键,外键是可以有重复的,可以是空值。一个表可以有多个外键。

primary key 是主键约束 , 表由列和行组成。 通常,表具有一列或多列,列的值唯一地标识表中的每一行。 此列或多列称为主键。由两列或更多列组成的主键也称为复合主键。

create table 表名(列名 类型 primary key);
  • primary key 相当于not nullunique结合的效果 .
  • 在实际开发当中, 每个表通常都会设置一个主键 , 但允许多个列放到一起共同作为一个主键(联合主键)

创建表中包含字段名为 id , 字段类型为int , 其中 id为主键 ,不为空且唯一

mysql> create table user(id int primary key,name varchar(20));Query OK, 0 rows affected (0.05 sec)mysql> desc user;+-------+-------------+------+-----+---------+-------+| 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 user values(12,'张'),(12,'李');ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

设置自增主键

MySQl 当中支持自增主键 , 在插入数据时 ,未设置初始值的情况下 , 会自动从 1 开始自增

create table 表名(列名 类型 primary key auto_increment, ...);
#将user表中的主键id 设置为自增主键mysql> create table user(id int primary key auto_increment,name varchar(20));Query OK, 0 rows affected (0.05 sec)mysql> desc user;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || name  | varchar(20) | YES  |     | NULL    |                |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> insert into user values(null,'李'),(null,'张'),(null,'王');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0# 自增主键设置成功mysql> select * from user;+----+------+| id | name |+----+------+|  1 | 李   ||  2 | 张   ||  3 | 王   |+----+------+3 rows in set (0.00 sec)# id列的null并不是将id设置为null, 而是将id的赋值交给数据库来使用自增主键

MySQL当中数据量较大的时候 , 就需要使用多个主机进行储存, 本质上就是分表然后每个数据库服务器只储存一部分数据 , 这种情况下式就涉及到了"分布式系统中唯一id生成算法" , 利用不同时间下的时间戳和主机编号并结合随机因子 , 就可以得到唯一的id.

6 . 外键约束(foreign key)

外键用来让两张表的数据之间建立连接 , 从而保证数据的一致性和完整性 .

create table 表名(      字段名 数据类型,      foreign key(本表字段名) renferences 主表名(主键名或unique字段名))
alter table 添加外键的表名 add constraint 外键名称 foreign key(从表字段名) renferences 主表名(主表字段名)

外键约束常用来定义主表和从表的关系 , 外键约束定义在从表上 ,主表上必须具有主键约束或是unique约束

# 创建主班级表mysql> create table class(id int primary key comment'班级编号',                          name varchar(10) not null);Query OK, 0 rows affected (0.02 sec)# 创建学生表并建立外键约束mysql> create table student(id int primary key comment '学生编号',name varchar(20) not null,class_id int, -- 学生所在的班级编号foreign key(class_id) references class(id));Query OK, 0 rows affected (0.01 sec)# 主表副表结构mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(10) | NO   |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc student;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | NO   | PRI | NULL    |       || name     | varchar(20) | NO   |     | NULL    |       || class_id | int(11)     | YES  | MUL | NULL    |       |+----------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)#班级表中插入数据mysql> INSERT INTO class values(100,'java'),   (200,'web');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from class;+-----+------+| id  | name |+-----+------+| 100 | java || 200 | web  |+-----+------+#学生表插入数据mysql> insert into student values(1,'tom',100);Query OK, 1 row affected (0.02 sec)mysql> insert into student values(2,'jack',200);Query OK, 1 row affected (0.02 sec)#添加失败,300号班级在class表中不存在,由于外键约束的作用,插入失败mysql> insert into student values(3,'frx',300);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
行为说明(当在父表中删除/更新对应的记录时)
NO ACTION检查该记录是否有对应外籍,如果有则不允许删除/更新
RESTRICT检查该记录是否有对应的外键,有则不允许删除/更新
CASCADE(级联)检查该记录是否有对应的外键,如果有,则也删除/更新外键在子表中的记录
SET NULL检查该记录是否有对应的外键,如果有则设置子表中该外键的值为NULL
SET DEFAULT父表有变更时,子表将外键列设置成一个默认的值(innodb不支持)
alter table 子表 add constraint 外键名 foreign key (字段名) references 父表(字段名) on update 行为 on delete 行为;

将 student 表中的外键删除更新行为设置为 update set null 和 delete set null

#设置 student 表中的外键mysql> alter table student add constraint class_id foreign key(class_id) references class(id) on update set null on delete set null;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0#更新父表中的 id mysql> update class set id = 300 where name = 'web';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0#查询子表中的全部信息mysql> select * from student;+----+------+----------+| id | name | class_id |+----+------+----------+|  1 | tom  |      100 ||  2 | jack |     NULL |+----+------+----------+2 rows in set (0.00 sec)

定义的任何约束可以使用带有DROP CONSTRAINT选项的ALTER TABLE命令删除 ,

ALTER TABLE table_name DROP CONSTRAINT fk_name;

如果使用的是MySQL,则可以使用更清晰的语法,如下所示:

ALTER TABLE table_name  DROP FOREIGN KEY fk_name;

示例:

-#删除外键约束mysql> alter table student drop foreign key class_id;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0#删除外键约束后,主表与从表再无关联,成功插入mysql> insert into student values(100,'张',550);Query OK, 1 row affected (0.00 se

一方面 , 外键的使用保证了数据库中数据的一致性和完整性 , 并且级联操作方便,减少了程序的代码量

另一方面 , 每次进行 update 和 delete 都需要考虑外键约束 , 导致测试数据极不方便 , 主从关系是确定的,如果哪天需求发生变化 , 会增加很多麻烦 .

所以 , 外键与级联更新适用于单机低并发,不适合分布式、高并发集群; 级联更新是强阻塞,存在数据库更新风暴的风 险; 外键影响数据库的插入速度 , 并发量不是很高的情况下可以使用外键


来源地址:https://blog.csdn.net/m0_56361048/article/details/128769643

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯