mysql 表的完整性约束

Wesley13
• 阅读 417

mysql 表的完整性约束

阅读目录

  •   概览
  •   not null
  •   unique
  •   primary key
  •   foreign key

概览

  为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

  约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

# NOT NULL :非空约束,指定某列不能为空; 
# UNIQUE : 唯一约束,指定某列或者几列组合不能重复
# PRIMARY KEY :主键,指定该列的值可以唯一地标识该列记录
# FOREIGN KEY :外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性

NOT NULL

是否可空,null表示空,非字符串 not null - 不可空 null - 可空

mysql 表的完整性约束 not null示例

mysql> create table t12 (id int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t12;
Empty set (0.00 sec)

mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

#不能向id列插入空元素。 
mysql> insert into t12 values (null);
ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into t12 values (1);
Query OK, 1 row affected (0.01 sec)

DEFAULT

我们约束某一列不为空,如果这一列中经常有重复的内容,就需要我们频繁的插入,这样会给我们的操作带来新的负担,于是就出现了默认值的概念。

默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

mysql 表的完整性约束 not null + default 示例

mysql> create table t13 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 222     |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 只向id1字段添加值,会发现id2字段会使用默认值填充
mysql> insert into t13 (id1) values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
1 row in set (0.00 sec)

# id1字段不能为空,所以不能单独向id2字段填充值;
mysql> insert into t13 (id2) values (223);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value

# 向id1,id2中分别填充数据,id2的填充数据会覆盖默认值
mysql> insert into t13 (id1,id2) values (112,223);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 112 | 223 |
+-----+-----+
2 rows in set (0.00 sec)

mysql 表的完整性约束 not null不生效

设置严格模式:
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效):
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE

唯一约束,指定某列或者几列组合不能重复

mysql 表的完整性约束 unique示例

方法一:
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

mysql 表的完整性约束 not null 和unique的结合

mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql 表的完整性约束 联合唯一

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

PRIMARY KEY

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之,它是用来独一无二地确认一个表格中的每一行数据。 主键可以包含一个字段或多个字段。当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键。 主键可以在建置新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。 主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合。

1.单字段主键

mysql 表的完整性约束 单字段主键

============单列做主键===============
#方法一:not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二:在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

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

#方法三:在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id); #创建主键并为其命名pk_name

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

# 方法四:给已经建成的表添加主键约束
mysql> create table department4(
    -> id int,
    -> name varchar(20),
    -> comment varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table department4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2.多字段主键

mysql 表的完整性约束 多字段主键

==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

AUTO_INCREMENT

约束字段为自动增长,被约束的字段必须同时被key约束

mysql 表的完整性约束 设置auto_increment

#不指定id,则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

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

了解知识

mysql 表的完整性约束 offset偏移量

#在创建完表后,修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | egon | male |
+----+------+------+
row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver:自增步长
    基于表级别
    create table t1(
        id int。。。
    )engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长:
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长(所有会话都生效)


#!!!注意了注意了注意了!!!
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略 ,这相当于第一步步子就迈大了,扯着了蛋
比如:设置auto_increment_offset=3,auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

步长:auto_increment_increment,起始偏移量:auto_increment_offset

FOREIKEY

多表 :

假设我们要描述所有公司的员工,需要描述的属性有这些 : 工号 姓名 部门

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

解决方法: 我们完全可以定义一个部门表 然后让员工信息表关联该表,如何关联,即foreign key

mysql 表的完整性约束 创造外键的条件

mysql> create table departments (dep_id int(4),dep_name varchar(11));
Query OK, 0 rows affected (0.02 sec)

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 创建外键不成功
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key 

# 设置dep_id非空,仍然不能成功创建外键
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint

# 当设置字段为unique唯一字段时,设置该字段为外键成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
Query OK, 0 rows affected (0.02 sec)

mysql 表的完整性约束 外键操作示例

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade  # 级连删除
on update cascade # 级连更新
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'教质部'),
(2,'技术部'),
(3,'人力资源部');


#再往子表employee中插入记录
insert into employee values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬银',3)
;


#删父表department,子表employee中对应的记录跟着删
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬银    |      3 |
+----+-----------+--------+
6 rows in set (0.00 sec)


#更新父表department,子表employee中对应的记录跟着改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬银    |      2 |
+----+-----------+--------+
6 rows in set (0.00 sec)

mysql 表的完整性约束 on delete(了解)

 . cascade方式
在父表上update/delete记录时,同步update/delete掉子表的匹配记录 

   . set null方式
在父表上update/delete记录时,将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  

   . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

   . Restrict方式
同no action, 都是立即检查外键约束

   . Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
5个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这