MySQL总结（二）数据的完整性 - 好文

概述

数据完整性，指保证用户输入的数据保存到数据库中是正确的，即数据库中的数据能正确反应实际情况。
为了确保数据的完整性，一般在创建表时给表中添加约束，也可修改表的约束。

数据的完整性是指数据的可靠性和准确性，数据完整性类型一般分三种：
1.实体完整性：通过唯一约束，主键约束或标识列属性来实现，如：PRIMARY KEY（主键约束）、UNIQUE 唯一值约束。
2.域完整性：通过数据类型（如数值类型、日期类型、字符串类型等限制类型）、非空约束(NOT
NULL)、默认值定义（DEFAULT）、CHECK检查(MySQL不支持check约束)来实现。
3.引用完整性：又叫做参照完整性，通过外键约束（FOREIGN KEY）来实现。引用完整性保证键值在所有表中一致，不能引用不存在的值。

1.实体完整性

实体完整性包含主键约束和唯一值约束。

* 1.1 主键约束
主键约束中，又包含主键PRIMARY KEY（单个主键、复合主键）和自增主键 AUTO_INCREMENT PRIMARY KEY。
一张表只能设置一个主键，值必须唯一，不允许为空，可以设置一列，也可以设置多列为复合主键(那么就是多列组合唯一，且都不为空)，主键就是索引。
如：
#复合主键： CREATE TABLE `daily_new` ( `jira_id` varchar(64) NOT NULL, `summary`
varchar(255) DEFAULT NULL, `report_time` datetime NOT NULL, `ongoingdays`
decimal(10,0) DEFAULT NULL, `week` varchar(10) DEFAULT NULL, `month` varchar(10)
DEFAULT NULL, `customer` varchar(10) DEFAULT NULL, CONSTRAINT pk_ID PRIMARY KEY
(`jira_id`,`report_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 #单独增加/删除主键的方法：
alter table daily_new add primary key(`jira_id`,`report_time`); alter table
daily_newdrop primary key; 自增主键： CREATE TABLE `clarify_issue` ( `id` int(11) NOT
NULL AUTO_INCREMENT, `statistics_time` datetime DEFAULT NULL, `clarify_type`
varchar(255) DEFAULT '', `sqa_remark` varchar(255) DEFAULT NULL, PRIMARY KEY (
`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5065 DEFAULT CHARSET=utf8 #为现有的表指定自增列 ALTER
TABLE clarify_issue MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT; #删除表中自增列
ALTER TABLE clarify_issue MODIFY COLUMN id INT NOT NULL;
注意：
1. 本文中描述的都是InnoDB存储引擎，MyISAM类型的存储引擎有一些区别
2. 《阿里巴巴java开发规范》中要求：【强制】表必备三字段：id, gmt_create, gmt_modified。
说明：其中id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为
datetime 类型，前者现在时表示主动创建，后者过去分词表示被动更新。
3. 《阿里巴巴java开发规范》中要求：【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即index 的简称。
4. 删除自增列，仍然是主键，但是没有自增长功能。

* 1.2 唯一值约束
一张表一列添加唯一值约束，保证值不重复，也可以有多个列添加唯一值约束，只允许一条记录为空值。
#创建表时指定唯一性约束 create table score (name VARCHAR(10) UNIQUE, score int not NULL );
#列增加唯一性约束alter table score add CONSTRAINT uc_name UNIQUE(name); #删除列的唯一性约束
alter table score drop index uc_name; #创建复合唯一性约束 create table stu (id INT, name
VARCHAR(10), score INT, CONSTRAINT uc_id UNIQUE(id, name) )ENGINE=InnoDB default
CHARSET=utf8;
注意：
如果表中现有记录是重复的，该列不允许添加唯一性约束。

2.域完整性

* 2.1 数据类型:（数值类型、日期类型、字符串类型）
* 2.2 非空约束：NOT NULL
确保当前列的值不为空。 CREATE TABLE student( Id INT pirmary KEY, name VARCHAR(50) NOT
NULL, sex VARCHAR(10) ); INSERT INTO student VALUES(1, 'tom', NULL); #指定列指定非空约束
ALTER TABLE student MODIFY COLUMN NAME INT NOT NULL; #删除非空约束 ALTER TABLE
student MODIFYCOLUMN NAME INT;
注意：
NULL表示所有的类型的值都可以是NULL，但是空字符串是不等于NULL的，0也不等于NULL，“ ”空格字符更不等于NULL。

* 2.3 默认值约束
插入新的记录时，如果该字段没有赋值，就使用默认值。 CREATE TABLE student( Id INT pirmary KEY, name
VARCHAR(50) NOT NULL, sex VARCHAR(10) DEFAULT ‘男’ ); INSERT INTO student1 VALUES
(1,'tom','女'); INSERT INTO student1 VALUES(2,'jerry',DEFAULT); #给表中一列添加默认值约束：
ALTER TABLE student MODIFY COLUMN name VARCHAR(50) DEFAULT '张三'; #删除表中一列的默认值约束：
ALTER TABLE student MODIFY COLUMN name VARCHAR(50) DEFAULT NULL;
3. 引用完整性（参照完整性）

外键约束：FOREIGN KEY
例：
CREATE TABLE student( sid INT pirmary KEY, name VARCHAR(50) NOT NULL, sex
VARCHAR(10) DEFAULT ‘男’ ); CREATE TABLE score( id INT, score INT, sid INT , --
外键列的数据类型一定要与主键的类型一致CONSTRAINT fk_score_sid FOREIGN KEY (sid) REFERENCES
student(id) ); #删除外键 ALTER TABLE score DROP FOREIGN KEY fk_score_sid;
第二种添加外键方式：
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES
stu(id);
注意：
外键所引用表的列必须是主键。

热门工具换一换