一、约束类型
- 主键约束:primary key(列级、表级)
- 唯一约束:unique(列级、表级)
- 非空约束:not null(列级)
- 检查性约束:check(列级、表级)(MySQL不支持check,无效)
- 外键约束:foreign key(表级)
- 默认值:default(列级)
二、定义约束
clazz表:cid、cname
create table clazz(
cid int primary key,
cname varchar(10)
);
student表:sid、scard、sname、cid、sage
列级约束:主键 唯一 非空 外键(无效) 默认
create table student(
sid int primary key,
scard varchar(10) unique,
sname varchar(10) not null,
cid int,
sage int default 10
);
表级约束:主键 唯一 非空(不支持) 外键 默认(不支持)
primary key无论是否命名,约束名都为primary
create table student(
sid int,
scard varchar(10),
sname varchar(10),
cid int,
sage int,
primary key(sid),
constraint stu_scard_uk unique(scard),
constraint stu_cid_fk foreign key(cid) references clazz(cid)
);
通用写法:
create table student(
sid int primary key,
scard varchar(10) unique,
sname varchar(10) not null,
cid int,
sage int default 10,
constraint stu_cid_fk foreign key(cid) references clazz(cid)
);
三、添加与删除约束
-- 1.主键约束
-- 列级约束
alter table student modify column sid int primary key;
-- 表级约束、删除
alter table student add primary key(sid);
alter table student drop primary key;
-- 2.唯一约束
-- 列级约束
alter table student modify column scard varchar(10) unique;
-- 表级约束、删除
alter table student add constraint stu_scard_uk unique(scard);
alter table student drop index stu_scard_uk;
-- 3.非空约束
-- 列级约束
alter table student modify column sname varchar(10) not null;
alter table student modify column sname varchar(10) null;
-- 4.检查性
-- 5.外键
-- 表约束、删除
alter table student add constraint stu_cid_fk foreign key(cid) references clazz(cid);
alter table student drop foreign key stu_cid_fk;
-- 6.默认
-- 列级约束、删除
alter table student modify column sage int default 10;
alter table student modify column sage int;
四、查看约束
结合desc命令和information_schema.table_constraints表查看约束
desc:
desc student;
结果:
information_schema.table_constraints:
select * from information_schema.table_constraints where table_name = 'student';
结果: