MySQL基础~数据约束primary key、unique、not null、default、foreign key

已于 2022-05-01 13:31:12 修改
阅读量2.5k 收藏 10
点赞数 6
分类专栏： MySQL 文章标签： mysql 数据库 sql java 外键约束
于 2022-04-29 22:34:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Merciful_Lion/article/details/124502806
版权

文章目录

数据库约束

约束可以进一步的对表中的数据进行限制，可以确保数据的正确性、有效性和完整性。

约束的种类：

  • PRIMARY KEY：主键
  • UNIQUE：唯一
  • NOT NULL：非空
  • DEFAULT ：默认
  • FOREIGN KEY：外键
主键

在数据库中，有时候我们可能记录的数据一模一样，为了区分这种相同的数据，我们可以给所有的数据设置一个新的字段，这个字段用来区分数据。

即主键的作用是用来唯一的标识记录.每一张表都有一个主键字段，每个数据都有一个主键，且每一个主键值都不相同，便于区分和管理数据。

通常情况下不会使用业务字段作为主键，一般会给每张表设计一个id字段，把id当做主键。主键没有含义，只要不重复、非空即可，就像身份证号码一样，每个人都要有，并且每个人的身份证号不能重复，就算两个人一模一样，也可以通过身份证号进行区分。

创建主键
在字段后添加 PRIMARY KEY 即可
create table 字段 字段名 PRIMARY KEY;

举例： 设置id为主键
mysql> create table hero1(
    -> id int primary key,
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)

主键不可为NULL,不可以重复，已经出现的主键必须唯一


删除主键
表示主键的字段还在，但是这个字段不再表示主键
alter table 表1 drop primary key;

举例： 删除id后看看id是否可以一样，一样表示id字段不再是主键
mysql> alter table hero1 drop primary key;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select*from hero1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   19 |
|  2 | c    |   20 |
|  3 | d    |   19 |
|  4 | e    |   20 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> insert into hero1(id,name)values(4,'f');
Query OK, 1 row affected (0.01 sec)

mysql> select*from hero1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   19 |
|  2 | c    |   20 |
|  3 | d    |   19 |
|  4 | e    |   20 |
|  4 | f    | NULL |
+----+------+------+
5 rows in set (0.00 sec)

主键自增
我们在添加主键时可能会出现添加重复的情况，为了避免这种情况，我们希望数据库在每次添加新的记录时，数据库自动生成主键字段的值


主键自增使用 AUTO_INCREMENT 关键字 使用时 关键字必须是整数类型

举例：插入数据看id是否自增
mysql> create table heros(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into heros(name,age) values('a',22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into heros(name,age) values('b',19);
Query OK, 1 row affected (0.01 sec)

mysql> insert into heros(name,age) values('c',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into heros(name,age) values('d',21);
Query OK, 1 row affected (0.01 sec)

mysql> select*from heros;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   22 |
|  2 | b    |   19 |
|  3 | c    |   20 |
|  4 | d    |   21 |
+----+------+------+
4 rows in set (0.00 sec) //id自增

id自增时，默认开始值是1，如果想要修改自增的起始值，可以如下操作：
alter table 表 auto_increment=起始值;

当我们对一张表设置了自增主键，如果使用DELETE清空了表里的数据，再次进行插入记录时，该记录的主键开始值是继续最后一条数据开始的，并非重新开始；如果使用TRUNCATE直接摧毁了这张表里的数据，那么再次插入数据时，会重新开始自增。


DELETE效果：
mysql> delete from heros;
Query OK, 4 rows affected (0.00 sec)

mysql> insert into heros(name,age) values('a',23);
Query OK, 1 row affected (0.01 sec)

mysql> select*from heros;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | a    |   23 |
+----+------+------+
1 row in set (0.00 sec) // delete清空后 id自增从5开始


TRUNCATE效果：
mysql> truncate table heros;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into heros(name,age) values('a',23);
Query OK, 1 row affected (0.01 sec)

mysql> select*from heros;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   23 |
+----+------+------+
1 row in set (0.00 sec) //truncate 这张表后，id自增从重新开始

总结：

  • DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值
  • TRUNCATE 摧毁表，然后重新建表，AUTO_INCREMENT重置为1；
唯一约束

在这张表中这个字段的值不能重复。这种约束可以用在一些数据不能重复的场景下，比如每个人的身份证号，不能重复，我们将身份证号存入数据库时，可以设置唯一约束，这样可以保证这张表中的每个身份证号都不相同，不存在输入错误的情况

实现唯一约束

通过 UNIQUE 关键字可以将字段约束为唯一

mysql> create table IDmessage(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> 身份证号 int UNIQUE
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into idmessage (name,身份证号)values('a',123);
Query OK, 1 row affected (0.00 sec)
mysql> select*from idmessage;
+----+------+----------+
| id | name | 身份证号 |
+----+------+----------+
|  1 | a    |      123 |
+----+------+----------+
1 row in set (0.00 sec)
mysql> insert into idmessage (name,身份证号)values('b',123);
ERROR 1062 (23000): Duplicate entry '123' for key 'idmessage.身份证号'
//这个错误表示身份证号字段具有唯一性，不能重复添加
非空约束

设置非空约束的字段必须设置值,不能为NULL，即字面意思，不为空

设置非空约束 在字段后添加NOT NULL即可
举例：
mysql> create table hero5(
    -> id int ,
    -> name varchar(20) NOT NULL,
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> select*from hero5;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | a    |   18 |
+------+------+------+
1 row in set (0.00 sec)
mysql> insert into hero5(name,age) values(null,18);
ERROR 1048 (23000): Column 'name' cannot be null
//错误提示name字段不能为null 
mysql>
默认

向表中添加数据时，如果不指定某个字段的值，就设置为默认值


字段名称 字段类型 DEFAULT 默认值

举例：将age字段默认值设为0
mysql> alter table hero5 modify age int default 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into hero5 (id,name) values(2,'b');
Query OK, 1 row affected (0.01 sec)

mysql> select*from hero5;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | a    |   18 |
|    2 | b    |    0 |
+------+------+------+
2 rows in set (0.00 sec) //第二条记录age默认为0
外键约束（重点）

外键约束用来在两个表之间建立连接，其中一个表的某个字段被另一张表中对应字段约束

使用外键约束时，至少需要两个表，被约束的表叫做从表（子表），另一张表就是主表（父表），两张表之间是主从关系

主表中进行关联的字段是主键，从表中进行关联的字段是外键.通过外键约束，可以两个表之间建立关联，让数据更加完整，关联性更强。

外键创建规则：

  1. 有主表才可以创建从表
  2. 主表必须实际存在
  3. 主表中必须包含主键
  4. 外键和主键的数据类型必须相同
  5. 外键和主键的数量必须相同
  6. 外键可以不是从表中的主键，但是必须和主表字段相对应

语法：

create table 表（字段1，字段2...字段n,foreign key(外键名称)references 主表名（主键名）属性）;

这里的属性有以下几种：

  • CASCADE：主表删除或者修改数据时，从表也会删除和修改
  • RESTRICT：删除和修改主表时，如果这个数据在从表中有关联，则不允许修改，如果没有关联，则可以修改
  • SET NULL：主表删除或修改数据时，从表会设为null
  • ON UPDATE/DELETE CASCADE:主表改变，从表改变。从表改变，主表不变。
  • ON UPDATE/DELETE RESTRICT：从表中没有的，主表可以改变。从表改变，主表不变。

创建外键约束

创建两个表，主表是class,从表是students

主表：
mysql> create table class(
    -> xuehao int primary key,
    -> name varchar(6)
    -> )engine=innodb;
Query OK, 0 rows affected (0.02 sec)


从表：
mysql> create table students(
    -> id int auto_increment primary key,
    -> uid int not null,
    -> name varchar(6),
    -> foreign key(uid) references class(xuehao)on update cascade on delete cascade)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

插入数据：
mysql> insert into class values(111,'a'),(222,'b'),(333,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into students values(1,111,'a'),(2,222,'b'),(3,333,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看表：
mysql> select*from class;
+--------+------+
| xuehao | name |
+--------+------+
|    111 | a    |
|    222 | b    |
|    333 | c    |
+--------+------+
3 rows in set (0.00 sec)

mysql> select*from students;
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  1 | 111 | a    |
|  2 | 222 | b    |
|  3 | 333 | c    |
+----+-----+------+
3 rows in set (0.00 sec)

验证外键约束的特性

从表删除记录对主表没有影响(从删主不删)

删除从表中id=1的记录
mysql> delete from students where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select*from students;
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  2 | 222 | b    |
|  3 | 333 | c    |
+----+-----+------+
2 rows in set (0.00 sec)

mysql> select*from class;
+--------+------+
| xuehao | name |
+--------+------+
|    111 | a    |
|    222 | b    |
|    333 | c    |
+--------+------+
3 rows in set (0.00 sec)

主表删除记录从表也会删除(主删从不见)

在主表中删除第一条记录
mysql> delete from class where xuehao=111;
Query OK, 1 row affected (0.01 sec)

mysql> select*from students;
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  2 | 222 | b    |
|  3 | 333 | c    |
+----+-----+------+
2 rows in set (0.00 sec)

先从表插入数据时，无法插入主键关联中不存在的记录，只有主表存在，从表才可以插入记录。

Salute-Y
关注
  • 6
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
SQL高级篇 中（NOT NULL，unique，primary key，foreign key，check，default）
qq_41011626的博客
03-17 1318
学习目标： 上篇文章主要介绍了SQL语句中的关键字用法，这篇文章中，将进行创建表时的约束关键字的用法！ 学习内容： 学习时间： 2021.3.17 学习产出： 写在前面： 对数据库字段的约束可以在创建表的时候进行约束，也可以在创建表之后使用alter table进行修改！ 1、NOT NULL约束 见字知意，该关键字的意思为：不为空！ 使用该关键字创建表，如下：我们可以知道，id字段和name字段被约束为not null； create table TestA( id int not null,
oracle 完整性约束 unique和not null,oracle完整性约束
weixin_42127937的博客
04-16 1888
完整性约束一、oracle中对表有五种约束条件非空(not null)唯一(unique)主键(primary key)外键(foreign key)check约束NOT NULL：指定列不能包含空值。UNIQUE：指定列的值或者列的组合的值对于表中所有的行必须是唯一的。PRIMARY KEY：表的每行的唯一性标识。FOREIGN KEY：在列和引用表的一个列之间建立并且强制一个外键关系。CHEC...
primary key与not null unique的异同点
12-29 3616
相同点： not null unique表示某列中数据是唯一的不能重复的，而primary key设为某一列时，也是自带唯一性属性的。 且数据都应为非空的。   不同点： 他们的应用场景其实不同 not null unique可以将表的一列或多列定义为唯一性属性，而primary key设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复。 primary key可以与外键配...
MySQL数据库约束（非空，唯一，默认，主键，外键约束(1)
最新发布
2401_84253894的博客
04-13 743
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**
Day03数据库基础之表的约束（not null、unique、primary key、foreign key）
m0_50036611的博客
04-05 1694
前言 一、表的约束 1、约束：对表中的数据进行限定，保证数据的完整性、正确性、有效性。 2、分类： 非空约束：not null 主键约束:primary key 唯一约束：unique 外键约束：foreign key 1.非空约束 （1）创建表时添加约束： create table stu( id int, name varchar(20) not null -- 添加非空约束 ); （2）创建表完后，添加非空约束： alter table stu modify name varchar(20)
MySQL四类约束primary key,auto_increment,unique,not null详解；
data_curd的博客
05-16 4950
primary key：主键约束 主键必须包含唯一的值 主键列不能包含null值 每个表都应该有一个主键，并且每个表只能有一个主键 创建primary key的方式有三种： 其一： create table cin_01( id int primary key, name varchar(32), address varchar(32), city varchar(32) ); 注意：如果有列类型或者列的长度需要修改，则可以用modify关键字，修改列类型或者长度 格式：alter tabl
基于MySQL数据库的数据约束实例及五种完整性约束介绍
09-09
MySQL数据库提供了五种主要的数据约束类型，分别是非空约束（NOT NULL）、唯一约束（UNIQUE）、主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）以及检查约束（CHECK）。下面我们将详细探讨这五种约束，并通过实例...
MySQL数据定义语言DDL的基础语句
09-08
例如，在创建`orders`表时，我们使用了`NOT NULL`约束确保`orders_title`和`orders_price`字段不能为空，`AUTO_INCREMENT`为`id`字段设置了自增属性，`PRIMARY KEY`定义了`id`为主键。 在使用MySQL DDL时，应根据...
mysql完整性约束实例详解
09-08
在MySQL中，有五种主要的完整性约束：`NOT NULL`、`DEFAULT`、`UNIQUE`、`PRIMARY KEY`和`FOREIGN KEY`。 1. `NOT NULL` 约束： - 这个约束强制字段不能存储NULL值。当定义一个字段为`NOT NULL`时，你确保该字段...
MySQL约束类型及举例介绍
09-09
在创建表时，可以为任何列添加NOT NULL约束，以确保该列总是包含有效数据。例如： ```sql CREATE TABLE Persons ( id INT NOT NULL, p_name VARCHAR(20) NOT NULL, department VARCHAR(20), address VARCHAR...
Mysql练习题基础操作 单表查询 多表查询 数据更新
04-11
- **非空约束**：`NOT NULL`确保字段值不能为空，如`id int(11) NOT NULL;` - **复合主键**：多个字段一起作为主键，如`student2`表的`primary key(id, name);` - **外键约束**：在`table2`中，`deptld`字段通过`...
Mysql中的约束
欢迎阅读我的博客
02-13 730
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N8RkMB8f-1676261205993)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666963659328.png)]在实际开发中尽量使用自然主键，因为主键只要做到不重复就行，不需要有意义 , 业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用。主键约束: primary key （简称PK）
数据库学习记录1 [SQL语句]
qq_44296442的博客
08-03 90
创建表相关： CREATE TABLE teacher( id INT PRIMARY KEY AUTO_INCREMENT, `name` CHAR(10) NOT NULL, age INT COMMENT '年龄', address CHAR(10) DEFAULT '中国', class_id INT, UNIQUE KEY(`name`), FOREIGN KEY(class_id) REFERENCES class(id) ); 在创建表
数据库系统原理与应用教程（029）—— MySQL 的数据完整性（二）：定义主键（primary key）
睿思达DBA
07-17 2388
主键约束
SQLite 基础语法学习
Looking的博客
01-21 1995
SQLite 是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。 就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。据说 SQLite 的语法和 MySQL 的大部分是一样的，而且从 Lite 的后缀可以看出，这应该像是一个 Mini 版本的 MySQL。虽说如此，我们还是来看看它和 MySQL 有哪些区别吧。 进入数
为什么数据库字段要使用NOT NULL？
luoyeyeyu的专栏
01-18 3817
转载自：为什么数据库字段要使用NOT NULL？ - 艾小仙 - 博客园 转载为了记录下来，我这边这方面理解确实不够深入，之前一直认为数据库必须设置NOT NULL 比较鸡肋，看了这篇文章之后，更加理解这方面的好处了 最近刚入职新公司，发现数据库设计有点小问题，数据库字段很多没有NOT NULL，对于强迫症晚期患者来说，简直难以忍受，因此有了这篇文章。 基于目前大部分的开发现状来说，我们都会把字段全部设置成NOT NULL并且给默认值的形式。 通常，对于默认值一般这样设置： 整形，我们一般使用
属性定义为 not null unique_Spring Data with MySQL （实体定义）
weixin_39719127的博客
11-30 386
Spring Data with MySQL节选自《Netkiller Spring Cloud 手札》多维度架构 - 知乎​www.zhihu.com选择数据库表引擎正常创建表会使用数据库默认引擎，有时数据库默认引擎并不是我们需要的，通过下面配置可以指定表引擎# Spring boot 1.x.xspring.jpa.properties.hibernate.dialect=org.hibern...
mysql not null unique 同时使用_SQL NOT NULL 约束 和 UNIQUE 约束
weixin_28854171的博客
01-19 1875
SQL UNIQUE 约束UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。CREATE TABLE 时的 SQL UNIQUE 约束下面的 SQL 在 "Pe...
CREATE TABLE user ( id varchar(30) NOT NULL COMMENT '用户 openid', session_key varchar(30) DEFAULT NULL COMMENT '用户 session_key', nickname varchar(20) DEFAULT NULL COMMENT '用户昵称', role int(2) DEFAULT NULL COMMENT '角色类型 0是管理员，1是用户，2是员工', phone_number varchar(20) DEFAULT NULL COMMENT '用户手机号', create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY phone_UNIQUE (phone_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';和CREATE TABLE user_account ( id int(10) NOT NULL AUTO_INCREMENT, user_id varchar(30) CHARACTER SET utf8mb4 NOT NULL COMMENT '用户id', password varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '用户密码', create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), KEY user_id_idx (user_id), CONSTRAINT user_id_idx FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;，用mybatis的注解查询数据
06-14
可以使用如下代码来使用mybatis的注解查询数据： ```java public interface UserMapper { @Select("SELECT * FROM user WHERE id = #{id}") User getUserById(@Param("id") String id); @Select("SELECT * FROM user_account WHERE user_id = #{userId}") UserAccount getUserAccountByUserId(@Param("userId") String userId); } ``` 其中，`@Select`注解表示执行的SQL语句，`@Param`注解表示传入的参数名，对应SQL语句中的占位符。在这个例子中，我们定义了两个方法，一个是根据用户id查询用户信息，另一个是根据用户id查询用户账号信息。同时，我们需要在`application.yml`文件中配置数据源和MyBatis的相关配置： ```yaml spring: datasource: url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8 username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.example.demo.entity ``` 其中，`mapper-locations`表示mapper文件所在的路径，`type-aliases-package`表示实体类所在的包名。需要注意的是，如果使用注解配置，就不需要mapper文件了。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值