1. MySQL约束
一、约束的作用
约束(constraint)是数据库用来提高数据质量和保证数据完整性的一套机制
约束作用在表列上，是表定义(DDL语句)的一部分
二、约束分类
非空约束 (not null)
insert，update数据时是不允许空值(null)
唯一性约束 (unique)
insert，update数据时不允许重值，可以允许空值，自动创建唯一性索引
主键约束 (primary key)
非空约束 + 唯一约束。主键的列不允许有重值和空值，自动创建唯一性索引
外键约束 (foreign key)
引用主键构成完整性约束。允许有空值，不允许存在对应主键约束的列所有数值以外的其它值
三、约束的定义方式
创建表时建立约束（事中）
建表之前就已经规划好了
列级定义
create table t (id int primary key,name char(10));
表级定义
create table t (id int,name char(10),primary key(id));
修改表时追加约束（事后）
建表之后根据需要追加
追加定义
alter table t add primary key(id);
四、查看约束
desc table_name;
show create table table_name\G
show keys from table_name; 看不了非空约束
show index from table_name;看不了非空约束
2、非空约束
非空约束用于确保其所在列的值不能为空值null
只有列级定义和追加定义
语法
列级定义
create table t (id int not null,name char(10));
追加定义
alter table t modify name char(10) not null;
测试
insert into t (id,name) values(null,null); 报错
insert into t (id,name) values(1,null);报错
insert into t (id) values(1); 报错，因为没有指定name值，默认用null填充
insert into t values(1,‘a’); 成功
运行为空
alter table t modify id int null;
设置默认值
alter table t modify id int null default 100;
alter table t modify name char(10) default ‘abc’;
insert into t (id) values(2); 成功,没有指定name值用默认值填充
drop table t; 删除t表，表列和行记录都没有了
3、唯一约束
表列中不允许有重复值，但是可以有空值
语法
列级定义
create table t1 (id int unique,name char(10));
表级定义
create table t2 (id int,name char(10),unique(id));
追加定义
create table t3(id int,name char(10));
alter table t3 modify id int unique;
测试
insert into t1 values(1,‘a’);
insert into t1 values(1, ‘a’); 报错，id中不允许重值，2个1
insert into t1 values(null, ‘b’); 成功，id允许有空值
删除unique约束
show keys from t1;
drop index id on t1;
desc t1;
drop table t1,t2,t3;
4. 主键约束
非空约束 + 唯一约束。主键的列不允许有重值和空值，自动创建唯一性索引
语法
列级定义
create table t1 (id int primary,name char(10));
表级定义
create table t2 (id int,name char(10),primary key(id));
追加定义
create table t3(id int,name char(10));
alter table t3 modify id int primary key;
测试
insert into t1 values(1,‘a’);
insert into t1 values(1, ‘a’); 报错，id中不允许重值，2个1
insert into t1 values(null, ‘b’); 报错，id不允许有空值
删除primary key约束
show keys from t1;
alter table t1 drop primary key;
show keys from t1; 自动创建的索引被删除
desc t1; 非空约束还在，没有被级联删除
alter table t2 add primary key(name); 失败，t2表已有主键，一个表不能有多个主键，可以建立非空+** 唯一约束代替
alter table t2 modify name char(10) not null unique; 成功
desc t2;drop table t1,t2,t3;
5. 外键约束
1.引用主键构成完整性约束。外键允许有空值，不允许存在对应主键约束的列所有数值以外的其它值。MySQL自动创建非唯一性索引
2.语法 (1个表)
表级定义
create table t1(id int,name char(10),pid int,primary key(id),foreign key(pid) references t1(id));
追加定义
create table t2(id int,name char(10),pid int);
alter table t2 add primary key(id);
alter table t2 add foreign key(pid) references t2(id);
3. 语法 (2个表)
表级定义
–先建立外键要引用的主表
create table c (cid int primary key,cname char(10));
– 再建立包含外键的从表
create table s (sid int,sname char(10),cid int,foreign key(cid) references c(cid));
追加定义
create table s1 (sid int,sname char(10),cid int);
alter table s1 add foreign key(cid) references c(cid);
4. 测试
insert into s (sid,sname,cid) values(1,‘张三’,101);报错，因为c表中cid不存在100的值，当前c表中没有任何记录
insert into c (cid,cname) values(101,‘1班’); c表中插入cid为101的值
insert into s (sid,sname,cid) values(1,‘张三’,101);再次插入成功
insert into s (sid,sname,cid) values(2,‘李四’,null); 插入成功，外键列可以有空值
4. 删除s表cid列中的外键约束
先要找出s表cid上外键约束的名称
mysql> select * from information_schema.key_column_usage where table_name=‘s’ and referenced_table_name is not null\G
然后再删除s表cid上的外键约束
mysql>alter table s drop foreign key s_ibfk_1;
