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);

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

文章来源: MySQL总结(二)数据的完整性

人吐槽 人点赞

猜你喜欢

发表评论

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:MySQL总结(二)数据的完整性