MySQL数据库-设置数据完整性

RFDeet

已于 2022-11-14 16:34:23 修改

阅读量2.6k

点赞数 1

文章标签：数据库 mysql

于 2022-10-16 15:06:22 首次发布

本文链接：https://blog.csdn.net/weixin_71712767/article/details/127339357

版权

数据完整性定义

数据完整性是指数据的正确性、相容性和一致性，数据库中的数据必须是真是可信、准确无误且相互对应一致的。

数据完整性分为三类：实体完整性、参照完整性、用户定义完整性。

实体完整性规则：

若属性A是关系R的主属性，则属性A不能取空值。

实体完整性规则要求表中的所有行都有一个唯一标识符。可以是一列或者几列的组合，称为主键。

MySQL中，强制实体完整性的方法有主键约束、唯一约束、自增属性。

参照完整性：

若属性F是基本关系R的外码，它与基本关系S的注码K相对应，则对于R中的每个元组在F上的值只允许两种可能，即要么取空值（F的每个属性均为空值），要么等于S中某个元组的主马值。其中，关系R与S可以是不同的关系，也可以是同一关系，而F与K定义在同一个域中。

MySQL中，强制参照完整性的方法有外键约束。

用户定义完整性：

用户定义完整性规则时针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。用户定义完整性是指一个列的输入有效性，是否允许为空值，例如学生的考试成绩必须在0~100之间，性别只能是“男”或“女”。

MySQL中，强制用户定义完整性的方法有非空约束，默认值和CHECK约束。

主键

创建带主键的表

用三种约束方式创建带主键的表

使用列级完整性约束方式来创建主键的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型 PRIMARY KEY[,
字段名 数据类型 [ NULL | NOT NULL][,...N]] );

使用标记完整性约束方式来创建主键的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL] [,...N],
PRIMARY KEY (字段名[,...n]));

使用给出约束名称的表级完整性约束方式来创建主键的CREATE TABLE语法如下所示：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL][,...N],
CONSTRAINT 表级约束名 PRIMARY KEY (字段名[,...N]));

参照实例：创建数据库dbtest1。在数据库dbtest1中创建表tbla，列cola，int类型，列colb，varchar（10）类型，列cola上有主键。

--列级完整性约束方式定义主键
create database dbtest1;
use dbtest1;
create table tbla
(cola int primary key,
colb varchar(10)
)engine = innoDB;
--表级完整性约束方式定义主键
create table tbla1(
cola int,
colb varchar(10),
primary key (cola)
)engine = innoDB;
--表级完整性约束方式定义主键，并给出主键约束名
create table tbla
(cola int,
colb varchar(10),
constraint PKtbla primary key(cola)
);

注意：以上为三个语句运行效果相同，三个语句可依次运行，运行成功后可进行逐渐调试。在不同版本中默认的引擎（engine）不同，8.0版本以上默认innoDB引擎，其他版本需声名。

为表添加主键

可以使用ALTER TABLE语句在表中添加或删除主键。

删除主键的ALTER TABLE语法如下表示：

ALTER TABLE 表名 DROP PRIMARY KEY;

使用标记完整性约束方式来添加主键的ALTER TABLE语法如下：

ALTER TABLE 表名 
ADD PRIMARY KEY(字段名[,...N]);

使用给出约束名称的表级完整性约束方式来添加主键的ALTER TABLE语法如下：

ALTER TABLE 表名
ADD CONSTRAINT 标记完整性约束名 PRIMARY KEY (字段名[,...N]);

参照实例：修改表tbla，删除表列cola上的主键。

ALTER TABLE tbla
DROP PRIMARY KEY;

删除主键后，测试字段cola值是否可以为空和可以重复。
参照实例：修改表tbla，添加表列cola和colb上的主键。

--无约束名
ALTER TABLE tbla
ADD PRIMARY KEY(cola,colb);
--有约束名
ALTER TABLE tbla
ADD CONSTRAINT PKtbla PRIMARY KEY(cola,colb);

注意：以上两个语句运行结果相同。上述示例cola和colb同时设为主键，两字段为复合主键。

候选键

当表中除主键字段外，还有其他字段需要保证取值不重复时，可以使用候选键。候选键用于实施实体完整性。候选键可以是表中的一个字段，也可以是表中多字段所构成的一个组合，能唯一标识记录，且又不含有多余属性，字段值允许为空。
在MySQL中，候选键与主键之间存在以下两点区别：一个表只能创建一个主键，但可以定义若干个候选键；定义主键时系统会自动创建PRIMARY KEY索引，尔的敬意候选键时，系统会自动创建UINIQUE系列。

创建带候选键的表

使用列级完整性约束方式来创建候选键的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型 UNIQUE[,]
字段名 数据类型[NULL | NOT NULL][,...N]);

使用表级完整性约束方式来创建候选键的CREATE TABLE语法如下表示：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL][,...N],
UNIQUE(字段名[,...N]));

表级完整性约束也可以通过CONSTRAINT关键字来给出约束的名称。
使用给出约束名称的表级完整性约束方式来创建候选键的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL],
CONSTRAINT 表级约束名 UNIQUE(字段名[,...N]));

参照实例：创建表tblb，列cola，int类型，列colb，varchar（10）类型，列cola上有主键，列colb上有候选键。

--列级完整性约束方式定义候选键
CREATE TABLE tblb
(cola INT PRIMARY KEY,
colb VARCHAR(10) UNIQUE);
--表级完整性约束方式定义候选键
CREATE TABLE tblb
(cola INT,
colb VARCHAR(10),
PRIMARY KEY(cola),
UNIQUE(colb)
);
--表级完整性约束方式定义候选键，并给出候选键约束名
CREATE TABLE tblb
(cola INT,
colb VARCHAR(10),
CONSTRAINT PKtblb PRIMARY KEY(cola),
CONSTRAINT UNIQUE(colb)
);

为表添加候选键

可以使用ALTER TABLE语句在表中添加或删除候选键。

删除候选键的ALTER TABLE语法如下表示：

ALTER TABLE 表名 DROP UNIQUE;

使用标记完整性约束方式来添加候选键的ALTER TABLE语法如下：

ALTER TABLE 表名 
ADD UNIQUE(字段名[,...N]);

使用给出约束名称的表级完整性约束方式来添加候选键的ALTER TABLE语法如下：

ALTER TABLE 表名
ADD CONSTRAINT 表级完整性约束名 UNIQUE(字段名[,...N]);

参照实例：修改表tblb，删除表列colb上的候选键。

ALTER TABLE tblb
DROP UNIQUE;

删除候选键后，测试字段colb值是否可以重复。
参照实例：修改表tbla，添加表列cola和colb上的主键。

--无约束名
ALTER TABLE tblb
ADD UNIQUE (cola,colb);
--有约束名
ALTER TABLE tblb
ADD CONSTRAINT PKtblb UNIQUE(cola,colb);

注意：以上两个语句运行结果相同。上述候选键是符合约束，添加候选键后应测试字段cola和colb值是否可以为空和重复，cola和colb的组合值是否可以重复。

自增

创建带自增字段的表

创建带自增约束字段的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型 [NULL | NOT NULL][AUTO_INCREMENT][,...N]);

创建带自增约束字段表后，测试字段是否可以实现自增功能。
给字段设置自增约束，可以达到自动填充自增数据的效果。
注意：尽管字段设置了自增约束，但也可以直接输入任意值，

为表添加自增字段

删除自增字段语法：

ALTER TABLE 表名 MODIFY 字段名 数据类型[NULL | NOT NULL][,...N];

增加自增字段语法：

ALTER TABLE 表名 MODIFY 字段名 数据类型 [NULL | NOT NULL][AUTO_INCREMENT][,...N];

注意：这里的AUTO_INCREMENT参数是自增初始值。
由此可见，修改AUTO_INCREMENT的值可以达到修改自增初始值的效果。

外键

数据库中，不同表的列或者同一表的不同列之间存在联系，这是因为它们都具有相同的业务含义，值也存在参照和引用的关系，这就是参照完整性。外键用于实施参照完整性，它与两个表相关，一个是被引用表，其主键列或候选列是被引用的列；另一个是引用表。引用表的外键列值必须引用被引用表的主键列值或候选键列值，也可以取空值。

创建外键规则

创建列表时，需遵守以下规则：

被引用表必须是已经创建或者正在创建的表。被引用表和引用表可以是同一个表，但多数情况下是两个表。
必须为被引用表创建主键。
不但指定被引用表，还要指定被引用表中的引用列或列组合，该引用列或列组合必须是被引用表的主键或者候选键。
外键中列的数目必须和被引用表的主键（或候选键）中列的数目相同。
外键中列的数据类型必须和被引用表的主键中列的数据类型相同。

MySQL要求在外键和引用的键上必须都要有索引，这样可以快速地进行外键检查，而不需要表扫描。因此在引用表中对外键列必须有索引，如不存在该索引，则MySQL会自动在引用表上创建此类索引。

外键操作规则

外键的更新规则和删除规则包含的选项相同，分别是级联规则Cascade、无操作规则No Action、限制规则Restrict和置空规则Set Null，默认选项是限制规则Restrict。

级联规则：不仅完成被引用表的更新或删除操作，系统还将会查找引用表中的对应记录，如果是更新则将引用表中记录的外键值跟着一起更新，如果是删除则将引用表中的记录一起删除。
限制规则：系统将会查找引用表中的对应记录，如果找到对应记录，系统将拒绝被引用表的更新或删除操作。
置空规则：不仅完成被引用表的更新或删除操作，系统还将会查找引用表中的对应记录，并将其外键列值改为空值。
无操作规则：系统将会查找引用表中的对应记录，如果找到对应记录，系统将拒绝被引用表的更新或删除操作，和限制规则一样。

可以分别使用表级完整性约束方式在CREATE TABLE语句中创造候选键约束。

创建带外键的表

使用表级完整性约束方式来创建候选键的CREATE TABLE语法如下：

CREATE TABLE 表名 
(字段名 数据类型[NULL | NOT NUll][,...N]),
FOREIGN KEY (字段名 REFERENCES 被引用表 (被引用字段)
[ON UPDATE {CASCADE | NO ACTION | RESTRICT | SET NULL}]
[ON DELETE {CASCADE | NO ACTION | RESTRICT | SET NULL}]);

其中，ON UPDATE 和ON DELETE选项用于设置外键的更新和删除规则，两个规则互不相关。

使用给出约束名称的表级完整性约束方式来创建候选键的CREATE TABLE 语法如下所示：

CREATE TABLE 表名 
(字段名 数据类型[NULL | NOT NUll][,...N]),
CONSTRAINT 表级约束名 FOREIGN KEY (字段名 REFERENCES 被引用表 (被引用字段)
[ON UPDATE {CASCADE | NO ACTION | RESTRICT | SET NULL}]
[ON DELETE {CASCADE | NO ACTION | RESTRICT | SET NULL}]);

为表添加外键

可利用ALTER TABLE语句在表中添加或删除外键。
删除外键语法如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名；

使用表级完整性约束方式来添加外键ALTER TABLE 语法如下：

ALTER TABLE 表名
ADD FOREIGN KEY(字段名)
[ON UPDATE {CASCADE | NO ACTION | RESTRICT | SET NULL}]
[ON DELETE {CASCADE | NO ACTION | RESTRICT | SET NULL}]);

使用给出约束名称的表级完整性约束方式来添加外键的ALTER TABLE语法如下：

ALTER TABLE 表名
ADD CONSTRAINT外键约束名 FOREIGN KEY(字段名)
[ON UPDATE {CASCADE | NO ACTION | RESTRICT | SET NULL}]
[ON DELETE {CASCADE | NO ACTION | RESTRICT | SET NULL}]);

注意：以上两语句运行效果相同。测试外键的更新规则和级联规则的效果。

非空

NULL是没有值或值未知。设置字段为空用关键字NULL表示，表示插入记录时可以省略该字段值。这里注意：NULL和空字符串、空格字符串以及0不同，其他三个有确切值。非空约束用于用户定义完整性。

创建带非空字段的表

创建带非空字段的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型 NOT NULL,
字段名 数据类型 [NULL | NOT NULL][,...n]);

注意：给字段设置主键，系统会自动给该字段设置非空。

为表的字段设置非空

删除非空约束的ALTER TABLE 语法如下：

ALTER TABLE 表名 MODIFY 字段名 数据类型 [NULL | NOT NULL];

添加非空约束的ALTER TABLE 语法如下：

ALTER TABLE 表名
MODIFY 字段名 数据类型 [NULL | NOT NULL];

默认值

在实际业务中，希望系统能在插入记录时为某些没有确定值的列自动赋予一个值，而不是设为NULL。默认值可以用来实施用户定义完整性。

对于没有设置自增的数字类型列，默认值是0；对于设置自增的数字类型列，默认值是计算所得到的下一个值。除TIMESTAMP以外的日期和事件类型，默认值是该类那个适当的“零”值；对于表中第一个TIMESTAMP列，默认值是当前的日期和时间。

可以在创建表时设置列默认值，也可以在创建表后再设置列默认值。

创建带默认值字段的表

创建带默认值字段的CREATE TABLE 语法如下所示：

CREATE TABLE 表名 
(字段名 数据类型 [NULL | NOT NULL] DEFAULT 默认值 [,...N]);

给字段设置默认值，可以达到自动填充默认值的效果。

为表的字段添加默认值

删除默认值的ALTER TABLE 语法如下：

ALTER TABLE 表名 MODIFY 字段名 数据类型 [NULL | NOT NULL][,...n];

添加默认值的ALTER TABLE 语法如下：

ALTER TABLE 表名
MODIFY 字段名 数据类型 [NULL | NOT NULL] DEFAULT 默认值[,...n];

CHECK约束

在插入记录时，表中的列值不仅必须与该列的数据类型相一致，还应具备合理的业务意义。这种对列值的进一步限制可以通过CHECK约束来完成。CHECK约束只在添加和更新记录时起作用，删除记录时不起作用。CHECK约束可以用来实施用户定义完整性。

CHECK约束是一个包含表中列值的逻辑表达式，返回值为TRUE或FALSE。只有使该逻辑表达式TRUE的列值才能被数据库保存。

CHECK 约束规则

CHECK约束中的逻辑表达式必须遵守下列规则：

允许使用非生成列和生成列，但具有自动递增属性的列和其他表中的列除外。
允许使用文本、确定性内置函数和运算符。如果给定表中的相同数据，多个调用独立于所连接的用户产生相同的结果，那么函数就是确定性函数，不确定性函数不能用在表达式中。
不允许使用存储函数和用户定义函数。
不允许使用存储过程和函数参数。
不允许使用变量（系统变量、用户定义变量和存储程序局部变量）。
不允许使用子查询。
禁止在CHECK约束中使用的列上执行外键引用操作（更新世、删除时）。同样，在外键引用操作中使用的列上禁止CHECK约束。
可以在创建表时设置CHECK约束，也可以在创建表后再设置CHECK约束。

创建带CHECK约束的表

使用列级完整性约束方式来创建CHECK约束的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL] CHECK(表达式),
[字段名 数据类型[NULL | NOT NULL] [,...n]]);

使用表级完整性约束方式来创建CHECK约束的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL] [,...n],
CHECK (表达式)[[NOT] ENFORCED ]);

使用给出约束名称的表级完整性约束方式来创建CHECK约束的CREATE TABLE语法如下：

CREATE TABLE 表名
(字段名 数据类型[NULL | NOT NULL] [,...n],
CONSTRAINT 表级约束名 CHECK (表达式)[[NOT] ENFORCED ]);

语句中的表达式是约束条件，是逻辑表达式，对于表的每一行，该表达的值是TRUE或FLASE。ENFORCED 选项设置CHECK约束是否强制执行约束。如果省略或指定为强制，则创造并强制执行约束。NOT ENFORCED选项表示指定为未强制，则将创建约束但不强制执行约束。

给字段设置CHECK约束，可以达到限定字段值范围的效果。

为表添加CHECK约束

可利用ALTER TABLE语句在表中添加或删除CHECK约束。
删除CHECK约束语法如下：

ALTER TABLE 表名 DROP CHECK CHECK 约束名；

使用表级完整性约束方式来添加CHECK约束的ALTER TABLE 语法如下：

ALTER TABLE 表名
ADD CHECK(表达式);

使用给出约束名称的表级完整性约束方式来添加CHECK约束的ALTER TABLE语法如下：

ALTER TABLE 表名
ADD CONSTRAINT 表级完整性约束名 CHECK(表达式);