SQL完整性约束

完整性约束

DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供针对插入的数据进行检查。

• 开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法的数据，则会自动报错，拒绝插入
• 实际有6种约束，可以分为3大类完整性约束。有些老外的书种提出的是4种【域完整性】

1. 实体完整性，由主键约束实现
2. 参照完整性，由外键约束实现
3. 用户自定义完整性，由其他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)));