mysql修改表的完整性限制_MySQL 之 表的完整性约束

为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

NOT NULL : # 非空约束,指定某列不能为空;

UNIQUE : # 唯一约束,指定某列或者几列组合不能重复

PRIMARY KEY : # 主键,指定该列的值可以唯一地标识该列记录

FOREIGN KEY : # 外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性

1、not null 非空约束

create table t1(id int not null,name char(12)); # 数字型设置为非空 当不写时 默认插入0

create table t2(id int,name char(12) not null); # 字符串设置为非空 当不写时 默认插入空字符串

# 设置严格模式:

不支持对not null字段插入null值

不支持对自增长字段插入”值

不支持text字段有默认值

直接在mysql中生效(重启失效):

mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效):

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2、default 设置默认值

​创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值。

create table t3(id int,name char(12),sex enum('male','female') default 'male'); #设置性别的默认值为男

3、非空约束 和 默认值

create table t3(id int not null,name char(12) not null,sex enum('male','female') not null default 'male');

4、unique 唯一约束(值不能重复)

create table t5(id int unique, family char(12),name char(12)); # id 值唯一

5、联合唯一

create table t5(family char(12),name char(12),unique(family,name)); # 联合唯一(family 与 name 组合起来唯一)

create table t5(family char(12) not null,name char(12) not null,unique(family,name)); # 约束各自不能为空 且联合唯一

6、唯一 + 非空

​第一个被设置了非空 + 唯一约束会被定义成主键 primary key, 主键在整张表中只能有一个

create table t6(id int not null unique, name char(12) not null unique);

7、primary key 主键

​主键为了保证表中的每一条数据的该字段都是表格中的唯一值,也就是,主键能够用来独一无二的确认一个表格中的每一行数据。

​主键可以包含一个字段或多个字段,当主键包含多个字段时,称为组合键,也就是联合主键。

​主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合。

create table t6(id int primary key, name char(12) not null unique);

create table t5(family char(12) ,name char(12),primary key(family,name)); # 约束各自不能为空 且联合唯一 还占用了整张表的主键

8、auto_increment (自动增加) 对某一列设置自增

create table t6(id int auto_increment, name char(12)); # 会发生报错, 因为 auto_increment 得在key情况下使用(unique 和 primary key)

create table t7(id int unique auto_increment, name char(12) primary key) ; # 设置id自增,并设置name为主键

create table t8(id int primary key auto_increment, name char(12)) ; # 将id设置为主键,并设置它为自增

create table t9(id int unique auto_increment, name char(12)) auto_increment=100000;

# 设置id自增,且从100000开始自增

(1)、正常所有的操作都无法改变auto_increment 的自动计数,也没有必要去改变它

delete from t7; # 清空表数据但却不能重置auto_increment计数

truncate table t7; # 清空表并且重置auto_increment计数

(2)、 修改 auto_increment

alter table 表名 auto_increment = n; # 修改表的auto_increment

alter table t7 auto_increment = 1000; # 修改t7表的auto_increment自增从1000开始

9、foreign key 外键

(1)、有无外键的区别

1、没有建立外键时:

create table stu(id int,name char(12),class_id int);

create table class(cid int,cname char(12));

insert into stu values (1,'cai',1),(2,'yong',1);

insert into class values(1,'python');

insert into class values(2,'java');

select * from stu,class where class_id = cid;

delete from stu where id = 1;

delete from class where cid = 1;

# 总结:在没有建立外键时,数据可以随意的进行增删改

2、建立外键 stu2 class2:

create table class2(cid int unique,cname char(12));

create table stu2(id int,name char(12),class_id int,foreign key(class_id) references class2(cid)); # 将表class2的cid设置为表stu2的外键

insert into class2 values(1,'python');

insert into stu2 values (1,'cai',1),(2,'yong',1);

delete from class2 where cid = 1;

insert into class2 values(2,'java');

update class2 set cid = 1 where cid = 2; # 不能修改

# 总结:有了外键约束之后不能再进行随意的增删改

(2)、级联更新

1、stu3 class3 级联更新:

create table class3(cid int primary key,cname char(12));

create table stu3(id int,name char(12),class_id int,foreign key(class_id) references class3(cid) on update cascade); # 将表class3中的cid设置为表stu3的外键,并建立级联更新,可以同时进行增删改

insert into class3 values(1,'python');

insert into stu3 values (1,'cai',1),(2,'yong',1);

update class3 set cid = 2; # 修改了class3中的cid,stu3中相关的数据也会跟着变化,是on update cascade设置导致的

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页