约束类型
查看表约束语句
#information_schema数据库名(系统库) #table_constraints表名称(专门存储各个表的约束) SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
根据约束条件约束可以分为
- NOT NULL 非空约束,规定某个字段不能为空
/*只能列级约束 */ create table ceshi ( id int not null, name varchar(18) not null, email varchar(30) ) -- 直接在字段名后面加 not null即可 alter table ceshi modify email varchar(30) not null
- UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
/*可以表级约束,或者列级约束 */ create table ceshi2( id int unique, email varchar(30), --表级约束 -- constraint uk_ceshi2_email unique key(email) --另外一种,或者不添加constraint 和 约束别名 unique key(email) )
复合约束
create table ceshi2( id int unique, email varchar(30), --表级约束 constraint uk_ceshi2_id_email unique key(email,id) )
删除约束
--可以直接约束字段或者约束别名 alter table ceshi2 drop index id ;
添加约束
--在实际开发中,不会删除主键约束,此处仅作示例 alter table test add primary key(id);
- PRIMARY KEY 主键(非空且唯一)约束
--列级约束 create table test( id int primary key, name varchar(20), email varchar(30) ) --复合表级约束 create table test( id int, name varchar(20), email varchar(30), pwd varchar(18), primary key(name,pwd) )
- FOREIGN KEY 外键约束
有关数据的引用完整性,一般在开发中不建议使用外键,一般在应用层面(即后端(Java或者其他后端语言))解决
原因:
外键约束是有成本的,需要消耗系统资源。
即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性。
/* 需要先创建主表,再创建从表 主表和从表的字段名的数据类型必须保持一致,字段名可以不一致 */ -- ①先创建主表,并且从表外键的创建,主表必须创建主键,并且外键必须于主表主键关联 CREATE table departments ( dep_id int primary key, dep_name varchar(20) ); --②创建从表 CREATE table employees( name varchar(20), department_id int, constraint uk_emplyees_depament_id foreign key(department_id) references departments(dep_id) ); desc employees; SELECT * FROM information_schema.table_constraints; WHERE table_name = 'employees';
主从同步 方法
/*创建表时,在从表的外键约束,最好是添加采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。*/ CREATE table departments ( dep_id int primary key, dep_name varchar(20) ); --②创建从表 CREATE table employees( name varchar(20), department_id int, foreign key(department_id) references departments(dep_id) on update cascade on delete set null );
- CHECK 检查约束
MySQL5.7不支持此约束,MySQL8.0以上支持
create table salary( salary decimal(10,2) check(salary > 2000) ); SELECT * FROM salary; insert into salary values (20000); --在MySQL8.0以上,应该会提示报错插入失败 insert into salary values (1900);
- DEFAULT 默认值约束
create table emp( name varchar(20), id int auto_increment primary key , salary decimal(20,2) default 20000 ); insert into emp(name) values('nanchengyu'); SELECT * FROM emp; --删除default alter table emp modify salary decimal(10,2) ; desc emp;
面试1、为什么建表时,加 not null default '' 或 default 0
答:不想让表中出现null值。
面试2、为什么不想要 null 的值
答:(1)不好比较。null是一种特殊值,比较时只能用专门的is null 和 is not null来比较。碰到运算符,通常返回null。
(2)效率不高。影响提高索引效果。因此,我们往往在建表时 not null default '' 或 default 0
面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗?
在MySQL中,默认AUTO_INCREMENT的初始值是1,每新增一条记录,字段值自动加1。设置自增属性(AUTO_INCREMENT)的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一条记录,同时指定id值为5,则以后插入的记录的id值就会从6开始往上增加。添加主键约束时,往往需要设置字段自动增加属性。
面试4、并不是每个表都可以任意选择存储引擎? 外键约束(FOREIGN KEY)不能跨引擎使用。
MySQL支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。所以说,存储引擎的选择也不完全是随意的。
补充
/* 只适合作用于整数型数据类型 只能作用于唯一约束,即unique,和primary key */ create table test1( id int primary key auto_increment, name varchar(20) ) --开发时,一段给主键作用于响应字段,插入相应字段数据时,一般不要继续添加
使用约束
- 创建表时 create table
- 修改表时 alter table 增加/删除