MySQL学习之完整性约束详解

数据完整性指的是数据的一致性和正确性。完整性约束是指数据库的内容必须随时遵守的规则。若定义了数据完整性约束，mysql会负责数据的完整性，每次更新数据时，MySQL都会测试新的数据内容是否符合相关的完整性约束条件，只有符合完整性的约束条件的更新才被接受。

1、主键约束

主键就是表中的一列或多个列的组合，其值能唯一地标识表中的每一行。MySQL为主键列创建唯一性索引，实现数据的唯一性。在查询中使用主键时，该索引可用来对数据进行快速访问。通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。如果PRIMARY KEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARY KEY约束定义中所有列的组合值必须是唯一的。

可以使用两种方式定义主键来作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARY KEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY（col_name,...）语句。

例：创建表book_copy，将书名定义为主键

CREATE TABLE book_copy
(图书编号 varchar(6) NULL,
书名 varchar(20) NOT NULL PRIMARY KEY,
出版日期 date
);

当表中的主键为复合主键时，只能定义为表的完整性约束。

创建course表来记录每门课程的学生学号、姓名、课程号和学分。其中学号、课程号构成复合主键

CREATE TABLE course
(学号 varchar(6) NOT NULL,
姓名 varchar(8) NOT NULL,
课程号 varchar(3),
学分 tinyint,
PRIMARY KEY(学号，课程名)
);

原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则：

1、每个表只能定义一个主键。关系模型理论要求必须为每个表定义一个主键。然而，MySQL并不要求这样，即可以创建一个没有主键的表。但是，从安全角度应该为每个基本表指定一个主键。主要原因在于，没有主键，可能在一个表中存储两个相同的行。当两个行不能彼此区分时，在查询过程中，它们将会满足同样的条件，更新的时候也总是一起更新，容易造成数据库奔溃。

2、表中两个不同的行在主键上不能具有相同的值，这就是唯一性规则。

3、如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，该复合主键是不正确的，这条规则称为最小化规则。也就是说，复合主键不应该包含不必要的列。

4、一个列名在一个主键的列表中只能出现一次。

MySQL自动地为主键创建一个索引。通常，这个索引名为PRIIMARY。不过，也可以重新给改索引另起名。

例：创建course表来记录每门课程的学生学号、姓名、课程号和学分。其中学号、课程号构成复合主键，将主键创建的索引命名为INDEX_C

CREATE TABLE course
(学号 varchar(6) NOT NULL,
姓名 varchar(8) NOT NULL,
课程号 varchar(3),
学分 tinyint,
PRIMARY KEY INDEX_C(学号，课程名)
);

2、替代键约束

替代键像主键一样，是表的一列或一组列，他们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE

例：在表book中将图书编号作为主键，书名列定义为一个替代键。 

CREATE TABLE book
(
图书编号 varchar(20) NOT NULL,
书名 varchar(20) NOT NULL UNIQUE,
PRIMARY KEY(图书编号)
); 

在MySQL中替代键和主键的区别主要有以下几点：

1、一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且他们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3列上定义了另一个替代键，这两个替代键在C2列上重合了，这是MySQL允许的。

2、主键字段的值不允许为NULL，而UNIQUE 字段的值可以是NULL，但必须使用NULL或NOT NULL声明。

3、创建PRIMARY KEY约束时，系统自动产生PRIMARY KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。

3、参照完整性约束

只有图书目录表中有的图书才可以销售，因此，在Sell表中的所有图书必须是Book表有的图书，也就是说存储在Sell表中的所有图书编号必须存在于Book表的图书编号列中。同样Sell表中的所有身份证号也必须出现在Members表的身份证号列中。这种类型的关系就是参照完整性约束。参照完整性约束都是一种特殊的完整性约束，实现为一个外键。所以Sell表中的图书编号列和身份证号列都可以定义为一个外键。可以在创建表或修改表时定义一个外键声明

定义外键的语法格式：REFERENCES 表名 [ ( 列名 | (长度）] [ ASC | DESC ],...) ]

[ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

[ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

外键被定义为表的完整性约束，语法中包含了外键所参照的表和列，还可以声明参照动作。如果没有指定动作，两个参照动作就会默认地使用RESTRICT。

MySQL参照完整性约束目前只可以用在那些使用InnoDB存储引擎创建的表中，对于其他类型的表，MySQL服务器能够解析CREATE TABLE语句中的FOREIGN KEY语法，但不能使用或保存它。

要修改表的存储引擎，可以采用ALTER TABLE语句。例如，修改Book表的存储引擎为InnoDB，使用：ALTER TABLE book ENGINE=INNODB;

例：创建book_ref表，所有的book_ref表中图书编号都必须出现在Book表中，假设已经使用图书编号列作为Book表主键。

CREATE TABLE book_ref
(
图书编号 varchar(20) null,
书名 varchar(20) null,
出版日期 date null,
PRIMARY KEY（书名）,
FOREIGN KEY(图书编号)
REFERENCES Book(图书编号)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)ENGINE=INNODB;

当指定一个外键时，适用以下规则：

1、被参照表必须已经用1条CREATE TABLE语句创建了，或者必须是当前正在创建的表。在后一种情况下，参照表是同一个表。

2、必须为被参照表定义主键

3、必须在被参照表的表名后面指定列名（或列名的组合）。该列（或该列组合）必须是这个表的主键或替代键。

4、尽管主键不能够包含空值，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，该外键的内容就是正确的。

5、外键中列的数目必须和被参照表的主键列的数目相同

6、外中列的数据类型必须和被参照表的主键中列的数据类型相同

例：创建带有参照动作CASCADE的book_refl表 

CREATE TABLE book_refl
(
图书编号 varchar(20) null,
书名 varchar(20) not null,
出版日期 date null,
PRIMARY KEY(书名),
FOREIGN KEY(图书编号)
REFERENCES Book(图书编号)
ON UPDATE CASCADE
)ENGINE=INNODB;

4、CHECK完整性约束

主键、替代键和外键都是常见的完整性约束的例子。但是，每个数据库都还有一些专用的完整性约束。例如，Sell表中订购册数要在1~5000之间，Book表中出版时间必须大于1986年1月1日。这样的规则可以使用CHECK完整性约束来指定。

CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约束。

语法格式：CHECK(表达式）

例：创建表student，只考虑学号和性别两列，性别只能包含男或女 

CREATE TABLE student
(
学号 char(6) not null,
性别 char(2) not null,
CHECK(性别 IN('男','女'))
);

例：创建表student，只考虑学号和出生日期两列，出生日期必须大于1980年1月1日

CREATE TABLE student
(
学号 char(6) not null,
出生日期 date not null
CHECK(出生日期>'1980-01-01')
);

到此这篇关于MySQL学习之完整性约束详解的文章就介绍到这了,更多相关MySQL完整性约束内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！