MySqL约束与设计

约束

约束种类

1. 主键约束(primary key) - 特点：非空且唯一
   • 代理主键：没有任何业务意义
   • 自然主键：表中本身存在一个非空且唯一的有效字段
     (身份证号，学号，QQ号)
   • 自动增长(auto_increment)
2. 非空约束(not null) - 特点：不能为空
3. 唯一约束(unique) - 特点：唯一，不能重复
4. 外键约束(foreign key)

主键约束(primary key)

-- 主键约束
-- 在建表时创建
create table stu (
	id int(6) primary key auto_increment,
    name varchar(20),
    score int(5) comment '学分'
);

insert into stu values(1, '张三', 23);
-- 因为设置了主键自动增长，所以可以将id设置null
insert into stu values(null, '李四', 23); 

-- 在已有的表中添加主键约束
alter table stu modify id int(6) primary key;
alter table stu drop primary key; -- 主键没有自动增长才能删掉(不报错)

非空约束(not null)

-- 非空约束
-- 在建表时创建
create table stu (
	id int(6),
    name varchar(20) not null,
);

-- 在已有表中添加非空约束
alter table stu modify name varchar(20) not null;
alter table stu add(sex char(1) not null);

-- 删除非空约束
alter table stu modify name varchar(20);

-- 添加非空约束并且设置默认值
alter table stu modify sex char(1) not null default '男';

唯一约束(unique)

-- 唯一约束 
-- 建表时添加唯一约束
alter table stu add(snum int(11) unique);

-- 唯一约束，允许有多个 null 值
insert into stu(name, age) values("鲁智深", 23);
insert into stu(name, age) values("宋江", 25);

-- 将唯一约束移除 index 索引
alter table stu drop index snum;

-- 已有的表中添加唯一约束
alter table stu add unique index (snum, name); -- 一对
alter table stu add unique index (snum);

外键约束(foreign key)

-- 外键约束 
-- 创建表时添加外键约束
create table student (
	id int(6) primary key auto_increment,
    name varchar(20) not null,
    age int(2),
    cid int(6),
    constraint fk_stu_cls foreign key (cid) references `class`(id)
);
create table `class` (
	id int(6) primary key auto_increment,
    name varchar(10) not null unique
);

insert into class values(2, 'Java');
insert into student values(null, 'lucy', 18, 2);

-- 在已有的表中添加外键约束，cid
alter table stu add constraint fk_stu_cls1 foreign key (cid) references `class`(id);

数据库三大范式

• 1NF：数据库表的每一列都是不可分割的原子数据项
• 2NF：在1NF基础上消除部分函数依赖
• 3NF：在2NF基础上消除传递依赖

概念：

• 函数依赖：
  A —> B，通过A属性(属性组)，能确定唯一B属性的值
  例：(学号) —> 姓名
• 完全函数依赖：
• A —> B, A是属性组，B的值需要依赖于A属性组中所有的值
  例：(学号，课程名称) —> 分数
• 部分函数依赖：
• A —> B，A是属性组，B的值需要依赖于A属性组中的一些、一个值
  例：(学号，课程名称) —> 姓名
• 传递依赖：A —> B —> C，C依赖于B，B依赖于A
  例：学号 —> 系名 —> 系主任
• 码：通过一个属性，或者一个属性组，能确定其他所有属性的值
  例：(学号，课程名称)