第六章 数据完整性约束和表维护语句

6.1 数据完整性约束

　　1. 数据完整性约束

　　　　数据的完整性总体来说可分为以下4类，即实体完整性、参照完整性、域完整性和用户自定义完整性

　　　　实体完整性:

　　　　　　实体的完整性强制表的标识符列或主键的完整性 （通过约束，唯一约束，主键约束或标识列属性）

　　　　参照完整性:

　　　　　　在删除和输入记录时，引用完整性保持表之间已 定义的关系，引用完整性确保键值在所有表中一致。

　　　　域完整性:

　　　　　　限制类型（数据类型），格式（检查约束和规则）， 可能值范围（外键约束，检查约束，默认值定义，非空约束和规则）　　　　　　　　

　　　　用户自定义完整性:

　　　　　　用户自己定义的业务规则。

　　2 .字段的约束

　　　　MySQL支持的常用约束条件有6种：

　　　　　　主键（primary key）约束

　　　　　　外键（foreign key）约束

　　　　　　非空(not NULL)约束

　　　　　　唯一性( unique )约束

　　　　　　默认值( default)约束

　　　　　　自增约束（auto_increment）

　　　　　　删除(delete)约束

　　　　其中，检查( check)约束需要借助触发器或者MySQL复合数据类型实现。

　　　　（1）主键（primary key）约束

　　　　　　一张表中只允许设置一个主键，是一个字段或字段组（不建议使用）。

　　　　　　设置主键通常有两种方式：表级完整性约束和列级完整性约束。

　　　　　　如果一个表的主键是单个字段ID

　　　　　　　　如果用表级完整性约束，就是用PRIMARY KEY命令单独设置主键为ID列。

　　　　　　　　语法规则: PRIMARY KEY(字段名)

　　　　　　　　【例９】创建学生stu1表，用表的完整性设置学号sno字段为主键。

　　　　　　　　如果用列级完整性约束，就是直接在该字段的数据类型或者其他约束条件后加上“primary key”关键字，即可将该字段设置为主键约束。

　　　　　　　　语法规则: 字段名 数据类型[其他约束条件] primary key

 　　　　　　　　【例10】创建学生stu2表，用列的完整性设置学号sno字段为主键。

 

　　　　　　如果一个表的主键是多个字段的组合，定义完所有的字段后，设置复合主键。

　　　　　　　　语法规则: primary key（字段名1，字段名2）

　　　　　　　　【例11】使用下面的SQL语句在studentInfo数据库中创建SC表，并将（sno,cno）的字段组合设置为SC表的主键。、

 

 　　　　（2）外键（foreign key）约束

 　　　　　　外键约束主要用于定义表与表之间的某种关系。

　　　　　　　　 （1）如果子表的记录“参照”了父表的某条记录，那么父表这一条记录的删除（delete）或修改（update）操作可能以失败告终。

　　　　　　　　（2）如果试图直接插入（insert）或者修改(update)子表的“外键值”，子表中的“外键值”必须是父表中的“主键值”，要么是NULL，否则插入( insert)或者修改( update)操作失败。

 　　　　　　　　

　　　　　　　　外键的也有两种方式，一种是在表级完整性下定义外键约束，一种是在列级完整性下定义外键约束

　　　　　　　　 表级完整性语法:

alter table table_name
    add [ constraint 外键名] foregin key [id](index_col_name, ……)
  references table_name(index_col_name, ……)
  [ on delete {cascade| restrict |set null | no action} ]
  [on update {cascade| restrict |set null | no action} ] 

 

　　　　　　　　【例13】将sc表的sno字段设置为外键，该字段的值参照(reference )班级student表的sno字段的取值.

 

 　　　　　　　　列级完整性上定义外键约束，就是直接在列的后面添加references命令。

 

　　　　　　　　表级完整性约束和列级完整性约束都是在CREATE TABLE语句中定义。还有另外一种方式，就是使用完整性约束命名字句CONSTRAINT,用来对完整性约束条件命名，从而可以灵活的增加、删除一个完整性约束条件。

　　　　　　　　完整性约束命名字句格式：

　　　　　　　　　　constraint <完整性约束条件名> [PRIMARY KEY 短语| FOREIGN KEY 短语 | CHECK 短语]

　　　　　　　　【例15】创建sc表，将sno字段设置为外键。

 

 　　　　（3）非空(not NULL)约束

　　　　　　如果某个字段满足非空约束的要求，则可以向该字段添加非空约束。. 非空约束限制该字段的内容不能为空，但可以是空白

　　　　　　语法规则: 字段名 数据类型 not null

　　　　　　【例16】将学生student表的姓名sname字段设置为非空约束

 

　　　　（4）唯一性( unique )约束

　　　　　　如果某个字段满足唯一性约束要求，则可以向该字段添加唯一性约束。与主键约束不同，一张表中可以存在多个唯一性约束，并且满足唯一性约束的字段可以取NULL值。

　　　　　　语法规则: 字段名 数据类型unique

　　　　　　【例17】创建班级classes表，班级名class_name字段设置为非空约束以及唯一性约束

 

 

　　　　（5）默认值( default)约束

　　　　　　如果某个字段满足默认值约束要求，可以向该字段添加默认值约束。

　　　　　　语法规则: 字段名 数据类型[其他约束条件] default默认值

　　　　　　【例18】创建课程course表，其up_limit字段设置默认值约束，且默认值为整数60

　　　　（6）自增约束（auto_increment）

　　　　　　 AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的ID。  

　　　　　　语法格式：CREATE TABLE table_name( 属性名 数据类型 AUTO_INCREMENT, …… );

　　　　　　【例19】创建表t_dept时，设置deptno字段为AUTO_INCREMENT和PK约束；

 

　　　　（7）删除( delete)约束

　　　　　　一个字段的所有约束都可以用alter table 命令删除

　　　　　　【例20】删除表sc中名称为sc_studen_fk的约束。

 

 

6.2 表维护语句

　　1. ANALYZE TABLE语句

　　　　语法规则: ANALYZE TABLE<表名1><表名2>...

　　　　【例6-7】更新数据库JXGL中表student的索引散列程度。

　　　　　　 ANALYZE TABLE JXGL. student;

　　2. CHECKSUM TABLE 语句

　　　　语法规则: CHECKSUM TABLE<表名1>[,<表名2>]...[QUICK|EXENDED]

　　　　【例6-8】对数据库JXGL的表student使用CHECKSUM TABLE语句获取一个校验和

　　　　　　CHECKSUM TABLE JXGL. student;

　　3. CHECK TABLE语句

　　　　语法规则: CHECK<表名1>[，<表名2>] [FOR UPGRADE|QUICK|FAST|MEDIUM|EXTENDED|CHANGED]

　　　　【例6-9】使用CHECK TABLE语句检查数据库JXGL中的表student。

　　　　　　 CHECK TABLE JXGL. student;

　　4. REPAIR TABLE 语句

　　　　语法规则: REPAIR TABLE<表名1>[,<表名2>]... [QUICK][EXTENDED][USE_FRM]

　　5. OPTIMIZE TABLE语句

　　　　语法规则: OPTIMIZE TABLE<表名1>[，<表名2>]...

　　　　【例6-11】利用OPTIMIZE TABLE语句来优化数据库JXGL中的表student。

　　　　　　 OPTIMIZE NO_WRITE_TO_BINLOG TABLE JXGL. student;