数据完整性是指数据的正确性和相容性,是为了仿真数据库中存在不符合语义的数据,即防止数据库中存在不正确的数据。
定义数据完整性通过CREATE TABLE或ALTER TABLE语句来定义。
关系模型的完整性规则是对关系的某种约束条件,在关系模型中,提供了实体完整性,参照完整性和用户定义完整性3项规则。
实体完整性规则(entity integrity rule):是指关系的主属性,即主码(主键)的组成不能为空,也就是关系的主属性不能是空值(NULL)。
实体完整性
实体完整性是通过主键约束和候选键约束来实现的。
主键可以是表中的某一列,也可以是表中多个列构成的一个组合;其中,由多个组合而成的主键也称为符合主键。
主键的规则:
1、每个表只能定义一个主键。
2、唯一性原则。主键的值,也称为键值,必须能够唯一表示表中的每一行记录,且不能为NULL。
3、最小化规则,复合组件不能包含不必要的多余列。当从一个复合主键中删除一列后,如果剩下的列构成的主键仍能满足唯一性原则,那么这个复合主键是不正确的。
4、一个列名在复合主键的列表中只能出现一次。
主键约束的方式:在CREATE TABLE或者ALTER TABLE语句中,使用PRIMARY KEY子句来创建主键约束。实现方式:
1、作为列的完整性约束,在定义某个列的属性时,加上PRIMAY KEY关键字实现
2、作为表的完整性约束,在定义表的所有列的属性后,加上PRIMAY KEY子句实现,并且加上字段。
如果主键仅由表中的某一列所构成,那么以上两种方法均可以定义主键约束;如果主键由表中多个列所构成,那么只能用第二种方法定义主键约束。另外,定义主键约束后,MySQL会自动为主键创建一个唯一索引,默认名为PRIMARY,也可以修改为其他名称。
候选键约束
一个属性集能唯一标书元组,且又不含有多余的属性,那么这个属性集称为关系的候选键。
候选键可以是表中的某一列,也可以是表中多个列构成的一个组合。候选键的值必须是唯一的,且不能为空。
候选键与主键的区别:
1、一个表只能创建一个主键,但可以定义若干个候选键
2、定义主键约束,系统会字段创建PRIMARY KEY索引,而定义候选键约束,系统会自动创建UNIQUE索引。
参照完整性
参照完整性规则就是定义外键和主键之间的引用规则,它是对关系间引用数据的一种限制。
参照完整性的定义为:若属性(或属性组)F是基本关系R的外码,它与基本关系S的主码K相对应,则对于R中每个元组在F上的值只允许两种可能,即要么取空值(F的每个属性值均为空值),要么等于S中某个元组的主码值。其中,关系R与S可以是不同的关系,也可以是同一关系,而F与K是定义在同一个域中。
参照完整性可以通过在创建表(CREATE TABLE)或者修改表(ALTER TABLE)时定义一个外键声明来实现
声明外键的基本语法:
[CONSTRAINT [SYMBOL]] FOREIGN KEY (index_col_name,…) reference_definition
reference_definition主要用于定义外键所参照的表、列、参照动作的声明和实施策略等4部分内容。语法:
REFERENCES tbl_name[(index_col_name,...)]
[MATCH FULL|MATCH PARTIAL|MATCHSIMPLE]
[ON DELETE reference_option] [ON UPDATE reference_option]
index_col_name的语法格式如下。
col_name [(length)] [ASC | DESC]
reference_option的语法格式如下。
RESTRICT | CASCADE | SET NULL | NO ACTION
参数说明:
(1)index_col_name:用于指定被设置为外键的列。
(2)tbl_name:用于指定外键所参照的表名。这个表称为参照表(或父表),而外键所在的表称作参照表(或子表)。
(3)col_name:用于指定被参照的列名。外键可以引用被参照表中的主键或候选键,也可以引用被参照表中某些列的一个组合,但这个组合不能是被参照表中随机的一组列,必须保证该组合的取值在被参照表中是唯一的。外键中的所有列值在被参照表的列中必须全部存在,也就是通过外键来对参照表某些列(外键)的取值进行限定与约束。
(4)ON DELETE | ON UPDATE:指定参照动作相关的SQL语句。可为每个外键指定对应于DELETE语句和UPDATE语句的参照动作。
(5)reference_option:指定参照完整性约束的实现策略。其中,当没有明确指定参照完整性的实现策略时,两个参照动作会默认使用RESTRICT
策略可选项
create table tb_student1 (id int auto_increment,name varchar(30) not null,sex varchar(2),classid int not null,birthday date,remark varchar(100),
primary key (id),
FOREIGN KEY (classid)
REFERENCES tb_class(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
设置外键遵守的规则:
(1)被参照表必须是已经存在的,或者是当前正在创建的表。如果是当前正在创建的表,也就是说,被参照表与参照表是同一个表,这样的表称为自参照表(Self-referencing Table),这种结构称为自参照完整性(Self-referential Integrity)。
(2)必须为被参照表定义主键。
(3)必须在被参照表名后面指定列名或列名的组合。这个列或列组合必须是这个被参照表的主键或候选键。
(4)外键中列的数目必须和被参照表中列的数据相同。
(5)外键中列的数据类型必须和被参照表的主键(或候选键)中对应列的数据类型相同。
(6)尽管主键是不能够包含空值的,但允许在外键中出现一个空值。这意味着,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
用户定义完整性
用户定义完整性规则(User-defined Integrity Rule)是针对某一应用环境的完整性约束条件,它反映了某一具体应用所涉及的数据应满足的要求
在MySQL中,非空约束可以通过在CREATE TABLE或ALTER TABLE语句中,某个列定义后面加上关键字NOT NULL来定义,用来约束该列的取值不能为空
CHECK约束
与非空约束一样,CHECK约束也可以通过在CREATE TABLE或ALTER TABLE语句中,根据用户的实际完整性要求来定义。它可以分别对列或表实施CHECK约束,其中使用的语法如下。
CHECK(expr)
其中,expr是一个SQL表达式,用于指定需要检查的限定条件。在更新表数据时,MySQL会检查更新后的数据行是否满足CHECK约束中的限定条件。该限定条件可以是简单的表达式,也可以是复杂的表达式(如子查询)。
命名完整性约束
在MySQL中,也可以对完整性约束进行添加、修改和删除等操作。其中,为了删除和修改完整性约束,需要在定义约束的同时对其进行命名。命名完整性约束的方式是在各种完整性约束的定义说明之前加上CONSTRAINT子句。
CONSTRAINT子句的语法格式:
CONSTRAINT<symbol> [PRIMARY KEY 短语 |FOREIGN KEY 短语 |CHECK 短语]
参数说明:
(1)symbol:用于指定约束名称。这个名称在完整性约束说明的前面被定义,在数据库里必须是唯一的。如果在创建时没有指定约束的名称,则MySQL将自动创建一个约束名称。
(2)PRIMARY KEY短语:主键约束。
(3)FOREIGN KEY短语:参照完整性约束。
(4)CHECK短语:CHECK约束。
更新完整性约束
对各种约束命名后,就可以使用ALTER TABLE语句来更新或删除与列或表有关的各种约束
删除完整性约束
使用ALTER TABLE语句,可以独立地删除完整性约束,而不会删除表本身。如果使用DROP TABLE语句删除一个表,那么这个表中的所有完整性约束也会自动被删除。删除完整性约束需要在ALTER TABLE语句中使用DROP关键字来实现。
语法格式:
DROP [FOREIGN KEY| INDEX| <symbol>] |[PRIMARY KEY]
参数说明:
(1)FOREIGN KEY:用于删除外键约束。
(2)PRIMARY KEY:用于删除主键约束。需要注意的是,在删除主键时,必须再创建一个主键,否则不能成功删除。
(3)INDEX:用于删除候选键约束。
(4)symbol:要删除的约束名称。
例如:ALTER TABLE tb_student1a DROP FOREIGN KEY fk_classid;
修改完整性约束:
不能直接修改完整性约束,若要修改,只能使用ALTER TABLE语句先删除该约束,然后增加一个与该约束同名的新约束
在ALTER TABLE语句中添加完整性约束的语法格式:
ADD CONSTRAINT <symbol> 各种约束
参数说明:
(1)symbol:为要添加的约束指定一个名称。
(2)各种约束:定义各种约束的语句