mysql数据库的约束

逍遥的潇

已于 2024-06-13 12:54:26 修改

阅读量197

点赞数 11

分类专栏： mysql 文章标签：数据库 mysql oracle adb

于 2024-06-12 16:15:48 首次发布

本文链接：https://blog.csdn.net/suli20221202/article/details/139628353

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、数据库的约束

mysql提供的约束

1.not null

指示某列不能存储null值。

2.nuique

保证某列的每行必须有唯一的值。

3.default

规定没有给列赋值时的默认值。

4.primary key

not null和 unique 的结合。确保某列(或两个列多个列的结合)有唯一标识，

5.foreign key

保证一个表中的数据匹配另一个表中的值的参照完整性。

6.check

保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略check子句.

注：约束可以连写

二、约束的效果

1unique 约束

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

不使用效果
mysql> insert into student values(null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| NULL | NULL |
+------+------+

 create  table student1(id int not null, name varchar(20)); 让id不能为空
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student1 values(null,null); 
ERROR 1048 (23000): Column 'id' cannot be null  插入id为空则报错

2.nuique 约束

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

 未使用时
 update student set id = null where name ='xiaoming';
 Query OK, 0 rows affected (0.00 sec)

mysql> insert into student1 values (1 , 'xiaoming');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student1 values (1 , 'xiaoming');   可以多行插入
Query OK, 1 row affected (0.00 sec)
mysql> select * from student1;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  1 | xiaoming |
|  1 | xiaoming |     表项中出现多个相同的值
+----+----------+

使用 unique 约束
mysql> create table student ( id int unique, name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student values(1,'xiaoming');
Query OK, 1 row affected (0.00 sec) 

mysql> insert into student values(1,'xiaoming');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'  这边进行了多次插入

mysql> select * from student
    -> ;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
+------+----------+     表项只有一次插入的值，无多次值，说明unique约束成功了
1 row in set (0.00 sec)

3.Default 约束

create table 列表名 (列名类型，列名，类型 default '关键字');

未使用如下：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

当我们使用了default 进行约束后，再插入时，就会在没有显示指定插入的值的时候生效

mysql> create table student (id int , name varchar(20) default '未命名');
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| id    | int(11)     | YES  |     | NULL      |       |
| name  | varchar(20) | YES  |     | 未命名    |       |
+-------+-------------+------+-----+-----------+-------+
mysql> insert into student (id) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 未命名    |
+------+-----------+
1 row in set (0.00 sec)

4.primary key(最重要的约束，一行记录身份标识)

create table 列表名 (列名类型 primary key，列名，类型 ');

【一个表里只能有一个身份标识，即只能有一个 primary key】

【虽然只有一个主键但也可以多个列，构成一个联合主键】

mysql> create table student (id int primary key ,name varchar(20)); 
Query OK, 0 rows affected (0.03 sec)    使用这个 primary kay 作为主键

mysql> insert into student values (1,'小明');
Query OK, 1 row affected (0.01 sec)          使用这个也可以正常插入

mysql> insert into student values (1,'小明');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

包含了 nuique 约束 所以说也不能重复插入

mysql> insert into student values (null,'小明');
ERROR 1048 (23000): Column 'id' cannot be null

同意包含了nuique 约束 也不能插入为空值（null)

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

对于带有主键的表来说，他每次插入修改/修改数据时，都会先进行查询操作。

5.foreign key 外键

描述了两个表之间的关联关系

父表约束子表；

子表受父表约束；

为什么要使用外键：

————————创建了两个表，一个班级，一个学生——————

1——————插入一个学生——————

mysql> insert into student values (1,'张三',100);
Query OK, 1 row affected (0.01 sec)

mysql> select * from class ;
+---------+--------+
| classid | name   |
+---------+--------+  
|       1 | 小班   |
|       2 | 中班   |
|       3 | 大班   |
+---------+--------+

————————————我们可以看到我们插入的 张三班级为 100 ，而我们创建的班级无100 
————————————所以我们认为这个插入是不合法的

mysql> select *from student;
+----+--------+---------+
| id | name   | classid |
+----+--------+---------+
|  1 | 张三   |     100 |
+----+--------+---------+

怎么使用外键

create table 表名 (列名类型 primary key,列名类型 , 表名类型 ,foreign key(当前表被约束的列) re
ferences 从那个表约束的(那个表的列名））；

指定外键约束时，父表中关联的这一列必须带主键或 unique

————————引用外键 创建了一个student 表  ，受到 class 表中 classid列约束—————— 
create table student (id int primary key,name varchar(20) , classid int ,foreign key(classid) references class(classid));


——————————给学生表插入一个——————
mysql> insert into student values (1,'张三',1);
Query OK, 1 row affected (0.01 sec)


——————————插入李四 班级 并不在我们创建的class表中——————
mysql> insert into student values (2,'李四',4);
————————报错——————
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))

————同样我们也不能直接修改子类已经引用的父表中的值——————
mysql> update student set classid =  4 where id = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))



mysql> select * from class;
+---------+--------+
| classid | name   |
+---------+--------+
|       1 | 小班   |
|       2 | 中班   |
|       3 | 大班   |
+---------+--------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+--------+---------+
| id | name   | classid |
+----+--------+---------+
|  1 | 张三   |       1 |
+----+--------+---------+
1 row in set (0.00 sec)

—————————父类中没有引用的值可以进行修改删除——————
mysql> delete from class where classid = 2;
Query OK, 1 row affected (0.01 sec)

——————一旦引用了就无法再修改即删除——————
mysql> delete from class where classid = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))

6.check（了解）

mysql 使用时不会报错吗但会忽略改约束

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

三、如何确保主键唯一

mysql 提供了 ”自增主键’ 的一种机制

主键常会使用 int / bigint

在插入数据时，不必手动指定主键值 ~~ 有数据库服务器自己给你分配一个主键

它会从 1 开始，进行递增的分配主键的值

1.自动分配

create table 表名(列名类型 primary key auto_increment, 表名类型);

缺点：

自动分配又一定局限性

如果是单个mysql服务器，没问题，

如果是有一个分布式系统，有多个 MySQL服务器构成的集群，这个时候依靠自增主键就不行了

他不会保证跟其他数据库中的 id 不重复

分布式系统 id 算法：http://t.csdnimg.cn/wTv1dhttp://t.csdnimg.cn/wTv1d

mysql> create table student(id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |   
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 
可以发现 extra 中出现了这个 auto——increment  表示他会自动给我们分配这个值。

————————————————————当我们插入一个值——————————————————

mysql> insert into student values (null,'小明');  

-----这里的 null 不是表示为空，而是交给这个关键字自动处理

————————————————————就会出现以下结果：————————————————

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
+----+--------+
1 row in set (0.00 sec)

——————————这边又插入一个数据————————————

mysql> insert into student values (null,'王强');
Query OK, 1 row affected (0.01 sec)


——————————可以看到 id 一列自动进行了添加
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | 王强   |
+----+--------+
2 rows in set (0.00 sec)

2.手动添加

也可以手动添加这个值，后续再使用关键字添加就会以新添加的值来递增

相当于使用一个变量来保存当前表id的一个最大值，

后续通过这个变量的值来进行分配

如果再指定了一个id 如果比他大会自动更新

【其中空余的空间会自动浪费】

——————————————-此处我们手动添加了一个 id 为 10.
mysql> insert into student values (10,'李四');

——————————————可以看到 同样我们可以手动添加————————————

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | 王强   |
|  3 | 张三   |
| 10 | 李四   |
+----+--------+

——————————————再自动添加一个 名为 王五 的。

mysql> insert into student values (null,'王五');

——————————————此时可以看到 它的自增数为最 新 添加的一个开始自增——————————————
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | 王强   |
|  3 | 张三   |
| 10 | 李四   |
| 11 | 王五   |
+----+--------+
5 rows in set (0.00 sec)