完整性约束
DBMS为了保证存储的数据都是完整有效的,避免存放垃圾数据,所以提供针对插入的数据进行检查。
- 开发人员指定对应的规则,由DBMS负责检查,如果试图插入不合法的数据,则会自动报错,拒绝插入
- 实际有6种约束,可以分为3大类完整性约束。有些老外的书种提出的是4种【域完整性】
- 实体完整性,由主键约束实现
- 参照完整性,由外键约束实现
- 用户自定义完整性,由其他4种约束实现
- 实际开发中除了主键约束外,其它约束一概不添加。其他的数据检查一般依赖应用程序实现,为了代码测试方便
1、非空约束,指定列值不允许为空
create table t1(id int not null); --不允许id列值为null
create table t2(id int null); -- 允许id列值为null
create table 3(id int);-- 允许id列值为null
插入null值有2种情况:直接插入null,或者没有在表上定义default而不插入数据,则默认null
2、默认约束default
一般默认约束经常和非空约束一起使用,当不插入数据时,默认值生效
create table t1(id int not null default 0); -- not null不是必须的
create table t2(id datetime default now()); -- 正确的,过去只有timestamp default
current_timestamp
3、主键约束,又叫做实体完整性,不允许数据重复存储
- 主键可以唯一标识一行数据,一个表中只能有一个主键,但是主键允许是由多个列构成
- 主键约束含义是非空、唯一
create table t1(id int primary key,....); -- 单一主键,
create table t2(
id int not null,...
primary key(id) -- 注意这里的主键约束允许使用复合主键,多个列构成的主键
);
复合主键中的任一列即使没有添加not null约束,也不允许为null
使用InnoDB存储引擎时,如果数据表没有设置主键,那么Innodb会给该表设置一个不可见,长度为6字 节的默认主键 row_id。Innodb维护了一个全局的dict_sys.row_id值,这个值,被所有无主键的数据表 共同使用,每个无主键的数据表,插入一行数据,都会是当前的dict_sys.row_id的值增加1
实row_id的值在存储时,数据长度为8字节,只不过Innodb只使用后6个字节。那么row_id的值,写到 数据表中时就有一下两个特点:
1.row_id写入表中的值范围,是从0-2^48-1。
2.当row_id的值为2^48时,再进行数据插入,那么row_id的后6个字节的值,就全部为0了。
也就是说,当row_id的值到了2^48次方-1后,再次插入数据,下一个值就是0,然后开始循环。不过和 自定义主键不同的是,row_id标识的主键,没有唯一性约束,当插入数据的row_id值,在表中已经存在 的话,那么写入的数据会"悄无声息"覆盖已存在的数据。
表尽可能都要设置主键,主键尽量使用bigint类型,21亿的上限还是有可能达到的,比如魔兽,虽然说 row_id上限高达281万亿,但是覆盖数据显然是不可接受的。 根据主键是否有业务含义可以分为业务主键和代理主键
4、唯一约束 unique
不允许添加唯一性约束的列出现重复值
可以没有null值约束,而且也不能针对null进行唯一性判定
create table t1(id int unique,name varchar(20));
insert into t1 values(1,'zhangsan');
insert into t1(name) values('zhao4');-- id为null
insert into t1(name) values('zhao4');-- id为null,两次null并不会报错
insert into t1 values(1,'li4'); -- 报错
mysql> create table t23(
-> id int,
-> name varchar(32),
-> unique(id,name)); -- 多个列的组合不允许重复,单一列允许重复
5、外键约束,又叫做参照完整性
6、检查约束check
mysql> create table t24(
-> id boolean default 1,
-> check(id in(1,0)));