本文共 7135 字,大约阅读时间需要 23 分钟。
什么是约束
约束:constraint
MySQL中的约束,实则是对数据表中数据的限制条件
约束的作用
约束保证数据的完整性和一致性
- 比如:设计数据表时,要求username字段不允许重复,这时候就需要在CREATE数据表时加入相应的约束。再比如注册时候必须填写邮箱、性别等。
约束的分类
按约束定义的位置可分
- 列级约束
- 表级约束
按约束的作用可分
主键 约束 | PRIMARY KEY | |
---|---|---|
外键 约束 | FOREIGN KEY | |
非空 约束 | NOT NULL | |
唯一 约束 | UNIQUE KEY | |
默认 约束 | DEFAULT |
列级约束、表级约束
区别
列级约束:只能应用于一列上。
表级约束:可以应用于一列上,也可以应用在一个表中的多个列上。即:如果你创建的约束涉及到该表的多个属性列,则必须创建的是表级约束(必须定义在表级上);否则既可以定义在列级上也可以定义在表级上此时只是SQL语句格式不同而已。
列级约束:包含在列定义中,直接跟在该列的其它定义之后 ,用空格分隔;不必指定列名。
表级约束:与列定义相互独立,不包含在列定义中;与定义用‘,’分隔;必须指出要约束的列的名称。注:因为在创建列级约束时,只需将创建列约束的语句添加到该字段(列)的定义子句后面;而在创建表级约束时,需要将创建表级约束的语句添加到各个字段(列)定义语句的后面,因为并不是每个定义的字段都要创建约束,所以必须指明需要创建的约束的列名。
举例说明
1 2 3 4 5 6 | Create Table project( 项目编号 INT CONSTRAINT pk_pno PRIMARY kEY, 项目名称 CHAR(20), 项目负责人 CHAR(20), CONSTRAINT un_pname_pm UNIQUE(项目名称,项目负责人) ); |
分析:“项目编号”字段设置为主键,主键约束名为pk_pno,此主键约束为列主键约束。“项目名称”和“项目负责人”的组合字段设置唯一性约束,此约束为表级约束)
具体说明
一:既可以创建列级约束又可以创建表级约束的:
1、主键约束(primary key constraint):
(1)定义列约束:CONSTRAINT <约束名> PRIMARY KEY (2)定义表约束:[CONSTRAINT <约束名>] PRIMARY KEY( <列名> [{<列名>}])2、唯一性约束(unique key constraint):
(1)定义列约束:[CONSTRAINT <约束名>] UNIQUE [KEY] (2)定义表约束:CONSTRAINT <约束名> UNIQUE [KEY] ( <列名> [{<列名>}])3、外键约束(foreign key constraint):
定义 列级约束:[CONSTRAINT <约束名>] FOREIGN KEY REFERENCES <外表名> 定义表级约束:[CONSTRAINT <约束名>] FOREIGN KEY REFERENCES <外表名>( <列名> [{<列名>}])二:只能创建列级约束的:
1,缺省约束(default constraint):
[CONSTRAINT <约束名>] DEFAULT 约束条件2,NULL 约束:
[CONSTRAINT <约束名>] [NULL | NOT NULL]主键约束 PRIMARY KEY
“主键(PRIMARY KEY)”的完整称呼是“主键约束”。 主键约束是一个列或者列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可以强制表的实体完整性。
涉及术语
- 主键约束
- 主键字段
- 主键值
三种术语的关系:
表中的某个字段添加主键约束后,该字段为主键字段,主键字段中出现的每一个数据都称为主键值
特点
- 保证数据的实体完整性
要求
一张表应该有主键字段,如果没有,表示该表无效
- 主键值:是当前行数据的唯一标识、是当前行数据的身份证号
- 即使表中两行记录相关数据相同,但由于主键值不同,所以也认为是两行不同的记录
按主键约束的字段数量分类
在MySQL的一个表中只有唯一的一个主键,不能有多列主键,但可以有复合主键。一个表中可以:单列做主键、多列做主键(复合主键)。
- 单一主键:给一个字段添加主键约束
- 复合主键:给多个字段联合添加一个主键约束(只能用表级定义)
单一主键(列级定义)
1 2 3 4 5 | mysql> create table t_user( -> id int(10) primary key, -> name varchar(32) -> ); Query OK, 0 rows affected (0.07 sec) |
单一主键(表级定义)
1 2 3 4 5 6 | mysql> create table t_user( -> id int(10), -> name varchar(32) not null, -> constraint t_user_id_pk primary key(id) -> ); Query OK, 0 rows affected (0.01 sec) |
复合主键(表级定义)
一定要在表级定义。
1 2 3 4 5 6 7 | mysql> create table t_user( -> id int(10), -> name varchar(32) not null, -> email varchar(128) unique, -> primary key(id,name) -> ); Query OK, 0 rows affected (0.05 sec) |
配合AUTO_INCREMENT
在MySQL数据库提供了一个自增的数字,专门用来自动生成主键值,主键值不用用户维护,自动生成,自增数从1开始,以1递增(auto_increment)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> create table t_user( -> id int(10) primary key auto_increment, -> name varchar(32) not null -> ); Query OK, 0 rows affected (0.03 sec) /*插入两行记录,id主键值会自动增加*/ mysql> insert into t_user(name) values('jay'); Query OK, 1 row affected (0.04 sec) mysql> insert into t_user(name) values('man'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +----+------+ | id | name | +----+------+ | 1 | jay | | 2 | man | +----+------+ 2 rows in set (0.00 sec) |
外键约束 FOREIGN KEY
外键约束(FOREIGN KEY)用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。
外键的主要作用是保持数据的一致性、完整性。例如,部门表 tb_dept 的主键是 id,在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。
- 主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表。
- 从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的表就是从表。
快速理解
之前创建表的时候都是在一张表中添加记录,比如如下表:
公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费。这个时候,解决方法:我们完全可以定义一个部门表,然后让员工信息表关联该表,如何关联,即foreign key。
我们可以将上表改为如下结构:
涉及到的术语
- 外键约束
- 外键字段
- 外键值
三种术语的关系:
某个字段添加外键约束之后,该字段称为外键字段,外键字段中每个数据都是外键值
特点
- 保持数据的一致性
- 完整性
- 实现一对一或者一对多关系
这也是为什么称为MySQL关系型数据库的原因
要求
- ★父表(子表所参照的表)和子表(具有外键列的表)必须使用使用相同的存储引擎,而且禁止使用临时表。
- ★外键对应的是参照完整性,一个表的外键可以为NULL,若不为空值,则每一个外键的值必须等于另一个表中主键的某个值。
- ★定义外键时,创建先创建父表。定义外键后,插入先插入父表数据,同理,删除先删除子表数据。
- 数据表的存储引擎只能为InnoDB。(编辑数据库的默认存储引擎,修改完需要重启MySQL服务)
- 外键列(加FOREIGN KEY关键词的一列)和参照列(外键列参照的一列)必须具有相似的数据类型,如果是数字数据类型,则数字长度或者是否有符号位必须相同;如果为字符数据类型长度可以不同。
- ★外键列和参照列(主键在创建的同时,会自动创建索引seq_in_index)必须创建索引。如果外键列不存在索引的话,MySQL将自动创建。
与主键的区别
一张表可以有多个外键,但是主键最多只有一个。
按外键约束的字段数量分类
单一外键:给一个字段添加外键约束
复合外键:给多个字段联合添加一个外键约束
表复合外键(表级定义)
1 | FOREIGN KEY(classno) REFERENCES t_class(cno); |
非空约束
用NOT NULL约束的字段不能为NULL值,必须给定具体的数据。
注意的是
NULL,表示空,并不是字符串中的空。两者需要区分开。
实例
1 2 3 4 5 6 7 8 9 10 | mysql> CREATE TABLE t12(id INT NOT NULL); #设置字段id不为空 Query OK, 0 rows affected (0.03 sec) mysql> DESC t12; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ row in set (0.01 sec) |
验证
1 2 | mysql> INSERT INTO t12 VALUES(); #不能插入空 ERROR 1364 (HY000): Field 'id' doesn't have a default value |
唯一约束
UNIQUE约束的字段,具有唯一性,不可重复,但可以为NULL。
列级约束
1 2 3 4 5 6 | mysql> create table t_user( -> id int(10), -> name varchar(32) not null, -> email varchar(128) unique -> ); Query OK, 0 rows affected (0.03 sec) |
表级约束
1 2 3 4 5 6 | mysql> create table t_user( -> id int(10), -> name varchar(32) not null, -> email varchar(128), -> unique(email) -> ); |
如果插入相同email会报错
1 2 3 4 5 | mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com'); ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email' |
扩展:联合唯一约束
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | # 创建services表 mysql> create table services( id int, ip char(15), port int, unique(id), unique(ip,port) ); Query OK, 0 rows affected (0.05 sec) mysql> desc services; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ rows in set (0.01 sec) #联合唯一,只要两列记录,有一列不同,既符合联合唯一的约束 mysql> insert into services values (1,'192,168,11,23',80), (2,'192,168,11,23',81), (3,'192,168,11,25',80); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from services; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192,168,11,23 | 80 | | 2 | 192,168,11,23 | 81 | | 3 | 192,168,11,25 | 80 | +------+---------------+------+ rows in set (0.00 sec) mysql> insert into services values (4,'192,168,11,23',80); ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip' |
更多可参考
默认约束
为某一字段提供默认值。
创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
实例
1 2 3 4 | create table tb1( id int not null, sex char defalut 'b' ); |
验证
1 2 3 4 5 6 7 8 9 10 11 | mysql> insert into tb1(id) values(1); #给t11表插一个空的值 Query OK, 1 row affected (0.00 sec) #查询结果如下 mysql> select * from t11; +------+------+ | id | sex | +------+------+ | 1 | b | +------+------+ row in set (0.00 sec) |
主要文献:
[部分概念来自C语言中文网] [大部分案例的代码块来自CSDN]注:案例中的SQL语句并非作者写的,存在规范问题,但不影响阅读。
转载地址:https://melodyjerry.blog.csdn.net/article/details/106617370 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!