【MySQL】数据表的约束
目录
一、约束种类
二、主键约束
三、唯一约束
四、非空约束
五、外键约束
六、外键的级联
七、索引
八、其他关键字
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
一、约束种类
约束名 | 约束关键字 | 描述 |
主键 | primary key | 字段值唯一,且不能为null |
唯一 | unique | 字段值唯一,且可以为null |
非空 | not null | 字段值不能为null |
外键 | foreign key | 保持关联数据的逻辑性 |
二、主键约束
用来唯一标识数据库中的每一条记录,通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
- 主键约束要求字段的值在全表必须唯一,而且不能为null值
- 建议主键使用数字类型,因为数字的检索速度非常快,而且还可以设置自增长
关键字 | 描述 |
primary key | 字段值唯一,且不能为null |
auto_increment | 表示自动增长(字段类型必须是整数类型) |
语句 | 描述 |
字段名 字段类型 primary key | 在创建表的时候给字段添加主键 |
alter table 表名 add primary key(字段名); | 在已有表中添加主键 |
caeate table 表名(列名 int primary key auto_increment) auto_increment=起始值; | 修改自增长的默认值起始值 |
alter table 表名 auto_increment=起始值; | 创建好以后修改起始值 |
delete 和 truncate 对自增长的影响
|
-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键create table st5 (id int primary key, -- id 为主键name varchar(20),age int)desc st5;-- 删除 st5 表的主键alter table st5 drop primary key;-- 添加主键alter table st5 add primary key(id);-- AUTO_INCREMENT 的默认开始值是 1,修改起始值-- 指定起始值为 1000create table st4 (id int primary key auto_increment,name varchar(20)) auto_increment = 1000;-- 创建好以后修改起始值alter table st4 auto_increment = 2000;
三、唯一约束
- 唯一约束要求字段值如果不为null,那么在全表必须唯一
关键字 | 描述 |
unique | 字段值唯一,且可以为null |
语句 | 描述 |
字段名 字段类型 unique | 表中某一列不能出现重复的值 |
alter table 表名 drop index 字段名; | 删除唯一约束 |
-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束create table st7 (id int,name varchar(20) unique)
四、非空约束
- 非空约束要求字段的值不能为null值
- null值是没有值,而不是""空字符串
关键字 | 描述 |
not null | 字段值不能为null |
default | 默认值 |
语句 | 描述 |
字段名 字段类型 not null | 某一列不能为 null |
字段名 字段类型 default 默认值 | 不传参数的话,指定一个默认值 |
如果字段设置了非空与唯一约束,该字段与主键的区别:
|
-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULLcreate table st8 ( id int, name varchar(20) not null, gender char(1))-- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州 create table st9 ( id int, name varchar(20), address varchar(20) default '广州')
五、外键约束
外键约束用来保证关联数据的逻辑关系
- 主表:一方,用来约束别人的表
- 从表:多方,被别人约束的表
关键字 | 描述 |
foreign key | 保持关联数据的逻辑性 |
语句 | 描述 |
create table 表名( ...... , [constraint] [外键约束名称] foreign key(外键字段名) references 主表名(主键字段名) ); |
新建表时增加外键 |
alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名); | 已有表增加外键 |
alter table 从表 drop foreign key 外键名称; | 删除外键 |
-- 创建从表 employee 并添加外键约束 emp_depid_fkcreate table employee(id int primary key auto_increment,name varchar(20),age int,dep_id int, -- 外键对应主表的主键-- 创建外键约束constraint emp_depid_fk foreign key (dep_id) references department(id));-- 删除 employee 表的 emp_depid_fk 外键alter table employee drop foreign key emp_depid_fk;-- 在 employee 表存在的情况下添加外键alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);
六、外键的级联
语句 | 描述 |
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade / on delete cascade; | 在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作 |
级联操作语法 | 描述 |
on update cascade | 级联更新,只能是创建表的时候创建级联关系。更新主表中的主键,从表中的外键列也自动同步更新 |
on delete cascade | 级联删除 |
-- 创建 employee 表,添加级联更新和级联删除create table employee(id int primary key auto_increment,name varchar(20),age int,dep_id int, -- 外键对应主表的主键-- 创建外键约束constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade)-- 添加外键,并设置级联更新ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY ( dep_id ) REFERENCES department ( id ) ON UPDATE CASCADE;-- 添加外键,并设置级联删除ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY ( dep_id ) REFERENCES department ( id ) ON DELETE CASCADE;-- 添加外键,并设置级联更新并删除ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY ( dep_id ) REFERENCES department ( id ) ON UPDATE CASCADE ON DELETE CASCADE;
七、索引
- 数据量很大,而且经常被查询的数据表可以设置索引
- 索引只添加在净长被用作检索条件的字段上面
- 不要在大字段上创建索引
关键字 | 描述 |
index | 索引 |
语法 | 描述 |
create table 表名( ...... , index [索引名称] (字段), ...... ); |
创建表时添加一个字段为索引字段 |
create index 索引名称 on 表名(字段); | 添加一个已有表的字段索引 |
alter table 表名 add index [索引名] (字段); | 添加一个已有表的字段索引 |
show index from 表名; | 查看一个表的索引 |
drop index 索引名称 on 表名; | 删除一个索引 |
-- 创建表时添加一个索引字段create table t_message(id int primary key, -- 主键默认是索引字段 type ENUM("公告","通报","通知") not null, -- 枚举类型,enum必须大写,三选一 context varchar(200) not null, create_time timestamp not null, index idx_type (type) -- 将type字段设置为索引字段);-- 删除索引(如果没有设置索引名,默认为字段名)drop index idx_type on t_message;-- 添加索引create index idx_type on t_message (type);-- 或者alter table t_message add index idx_type (type);-- 查看索引show index from t_message;
八、其他关键字
关键字 | 描述 |
auto_increment | 表示自动增长(字段类型必须是整数类型) |
unsigned | 无符号 |
default | 如果一列没有值,使用默认值 |
check | 检查约束,mysql 不支持 |
-- 创建t_teacher表,主键是整数无符号类型,并且自增长create table t_teacher(id int unsigned primary key auto_increment,name varchar(20) not null,tel char(11) not null unique);