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_new drop 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 MODIFY COLUMN 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);

注意：
外键所引用表的列必须是主键。