mysql 定义时限制取值范围 mysql怎么定义规则

转载

mob64ca1417736e 2023-10-18 23:49:56

文章标签 mysql 定义时限制取值范围主键完整性约束字段 文章分类 MySQL 数据库

AiryData 2017-03-27 00:02

预备阅读：MySQL中常用的数据类型 MySQL中表的操作

前言

前面已经学习了常用数据类型和表的操作，其中表的操作中提到了数据完整性约束条件，今天来详细说一下各种完整性约束条件。

关系模型的完整性规则是对关系的某种约束条件。对关系模型施加完整性约束，是为了在数据库应用中保障数据的正确性和一致性，防止数据库中存在不符合语义、不正确的数据，这也是数据库服务器最重要的功能之一。

完整性约束既能防止对MySQL数据库的意外破坏和非法存取，又能提高完整性检测效率，也能减轻我们的负担。下面说一下关系模型中的三类完整性约束。

定义实体完整性

实体完整性规则（Entity Integrity Rule）是指关系的主属性不能取空值，即主键和候选键在关系中所对应的属性都不能取空值。MySQL中实体完整性就是通过主键约束和候选键约束来实现的。

1、主键约束

主键是表中某一列或某些列所构成的一个组合。能够唯一地标识表中的一条记录。主键约束（Primary Key Constraint）要求主键列的数据唯一，且不允许为空。

主键约束实现的两种方式：

一种是列级完整性约束，在表中某个字段定义后加上关键字PRIMARY KEY即可。如studentNo char(10) PRIMARY KEY,。
一种是表级完整性约束，需要在表中所有字段定义最后添加一条PRIMARY KEY（index_col_name, ...）。如PRIMARY KEY(studentNo)。

比如我们重新创建学生表：

CREATE TABLE tb_student(
studentNo CHAR(10) PRIMARY KEY,
studentName VARCHAR(20) NOT NULL,
sex CHAR(2),
birthday DATE,
native VARCHAR(20),
nation VARCHAR(20),
classNo CHAR(6)
)ENGINE=InnoDB;

2、完整性约束的命名

与数据库中的表一样，可以对完整性约束进行添加、删除和修改等操作。首先需要在定义约束时对其进行命名，命名完整性约束的方法是，在各种完整性约束的定义说明之前加关键字CONSTRAINT和该约束的名字，语法格式如下：

CONSTRAINT<symbol>
{PRIMARY KEY (主键字段列表)
|UNIQUE (候选键字段列表)
| FOREIGN KEY (外键字段列表) REFERENCES tb_被参照关系(主键字段列表)
|CHECK(约束条件表达式)};

说明：symbol为指定的约束名字，必须是唯一的，默认MySQL会自动创建一个约束名字。

3、候选键约束

与主键一样，候选键可以是表中的某一列，也可以是表中某些列构成的一个组合。任何时候，候选键的值必须是唯一的，且不能为NULL，候选键可在CREATE TABLE 或ALTER TABLE语句中使用关键字UNIQUE来定义，实现方法与主键类似。看一个例子，创建班级表。

CREATE TABLE tb_class(
classNo CHAR(6) PRIMARY KEY,
className VARCHAR(20) NOT NULL UNIQUE,
department VARCHAR(30) NOT NULL,
grade SMALLINT,
classNum TINYINT
)ENGINE=InnoDB;

或者直接放在所有字段之后：CONSTRAINT UQ_class UNIQUE(className)。

MySQL中PRIMARY KEY 和UNIQUE之间的区别：

一个表只能创建一个PRIMARY KEY，但是可以定义多个UNIQUE。
定义PRIMARY KEY的列不允许有空值，定义UNIQUE的字段允许空值的存在。
定义PRIMARY KEY约束时，系统会自动产生PRIMARY KEY索引，而定义UNIQUE约束时，系统自动产生UNIQUE索引。

定义参照完整性

关系模型中实体与实体间的联系都是用关系来描述，因此可能存在着关系与关系间的引用。外键是一个表中的一个或一组属性，他不是这个表的主键，但是它对应另一个表的主键，外键的主要作用就是保证数据引用的完整性，保持数据的一致性。

参照完整性规则（Referential Integrity Rule）定义的是外键与主键之间的引用规则，即外键的取值或者为空，或者等于被参照关系中某个主键的值。

下面修改一下前面的学生表。

DROP TABLE tb_student;
CREATE TABLE tb_student(
studentNo CHAR(10),
studentName VARCHAR(20) NOT NULL,
sex CHAR(2),
birthday DATE,
native VARCHAR(20),
nation VARCHAR(20),
classNo CHAR(6) REFERENCES tb_class(classNo),
CONSTRAINT PK_student PRIMARY KEY(studentNo)
)ENGINE=InnoDB;

前面已经创建了表tb_class，而且classNo是主键，这里学生表里可以设置为外键，这个值参照班级表的主键classNo的值。

PS.外键只能引用主键和候选键。外键只可以用在使用存储引擎InnoDB创建的表中，其他的存储引擎不支持外键。

用户定义的完整性

除了前面两种完整性之外，还有一种特殊的约束条件，即用户定义的完整性规则（User-definedIntegrity Rule），它反映了某一具体应用所涉及的数据应满足的语义要求。

MySQL支持以下几种用户自定义完整性约束：

1、设置非空约束

非空约束是指设置的字段的值不能为空。比如之前我们添加的字段：studentNameVARCHAR(20) NOT NULL,中就设置了非空约束。

2、CHECK约束

与非空约束一样，也是在创建表或修改表的同时来定义的。CHECK约束需要指定限定条件，CHECK的语法是CHECK(expr);下面看一下实例，我们来创建一个课程表tb_course，并且设置约束，每16个课时对应1学分。

CREATE TABLE tb_course(
courseNo CHAR(10),
courseName VARCHAR(20) NOT NULL,
credit INT NOT NULL,
courseHour INT NOT NULL,
term CHAR(2),
priorCourse CHAR(6),
CONSTRAINT PK_course PRIMARY KEY(courseNo),
CONSTRAINT FK_course FOREIGN KEY(priorCourse)
REFERENCES tb_course(courseNo),
CONSTRAINT CK_course CHECK(credit= courseHour/16)
)ENGINE=InnoDB;

更新完整性约束

1、删除约束

DROP TABLE会删除所有完整性，但是并不经常这样做。看一下单独删除的语法。