首页 / MYSQL / MySQL表的完整性约束
MySQL表的完整性约束
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL表的完整性约束，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15508字，纯文字阅读大概需要23分钟。
内容图文
表的完整性约束
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
# NOT NULL ：非空约束，指定某列不能为空；
# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性NOT NULL
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
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
我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
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)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
唯一约束，指定某列或者几列组合不能重复
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'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)联合唯一：
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.单字段主键
============单列做主键===============
#方法一：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.多字段主键
==================多列做主键================
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约束
#不指定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)
FOREI KEY
多表 ：
假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key
创造外键的条件：
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)
外键操作示例：
#表类型必须是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)
on delete(了解)：
. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录
. set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
内容总结
以上是互联网集市为您收集整理的MySQL表的完整性约束全部内容，希望文章能够帮你解决MySQL表的完整性约束所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】