表的三范式和约束

一、表的三范式

1NF、要保证每个字段具有原子性不可再拆分、没有重复字段
2NF、表中要有主键且所有字段不能与主键部分依赖，如果不满足可以通过拆表来满足
3NF、确保每个字段都与主键直接相关，而不是间接相关，如果是可以通过消除冗余项或者拆表来满足
在使用关系型数据库的过程中总结的三项建立表的准则，如果遵守它能够最大效率地使用数据库

-- 学生表
    -- 学号 姓名 性别 身份证号 班级编号
    create table student(
        id int primary key,
        name varchar(30) not null,
        sex char default 'w',
        id_card char(18) unique,
        class_id int,
        foreign key(class_id) references class(id)
    );

-- 成绩表
    -- 学号 总分 语文 数学 英语
    create table grade(
        id int primary key,
        total double check (total>=0 && total <=300),
        c_grade double check (c_grade>=0 && c_grade <=100),
        m_grade double check (m_grade>=0 && m_grade <=100),
        e_grade double check (e_grade>=0 && e_grade <=100)
    );

-- 班级表
    -- 班级编号 教室 班主任 语文老师 数学老师 英语老师
    create table class(
        id int primary key,
        room_id int not null,
        class_tch int,
        c_tch int,
        m_tch int,
        e_tch int,
        foreign key(class_tch) references teacher(id),
        foreign key(c_tch) references teacher(id),
        foreign key(m_tch) references teacher(id),
        foreign key(e_tch) references teacher(id),
    );

-- 教师表
    -- 工号 姓名 性别 身份证号 科目 职称
    create table teacher(
        id int primary key,
        name varchar(30) not null,
        sex char default 'w',
        id_card char(18) unique,
        subject char(20),
        title char(20)
    );

二、表的约束

约束是对表和表中数据的限制，可以提高表中数据的准确性和可靠性，一般在创建表、修改表时使用

1、约束的分类

约束类型	名称	作用
not null	非空约束	字段的数据不能为空，例如学生姓名
default	默认值约束	给字段设置默认值后，当插入数据不提供该字段的数据时，数据库会自动填充为默认值，例如学生性别
unique	唯一约束	该字段数据不能重复，但是可以为null，例如身份证号
primary key	主键约束	非空且唯一，只有一个，例如学号
check	检查约束	设置一个条件，当插入数据不满足该条件时会失败，但是MySQL不支持\Oracle支持
foreign key	外键约束	用表A中的数据来确保表B中数据的准确性，例如表B中的某个字段值必须在表A中的某个字段的值中出现过，否则无法插入，例如教师表中的教师号必须在教师表中出现过

2、约束的设置方式

①创建表时设置约束

create table 表名(
	字段名 类型 约束名,
	...
);

②修改表时设置约束：只有not null\default 有效，可以增加、删除约束

alter table 表名 modify 原字段名 类型名 约束;
--  增加约束
alter table 表名 modify 原字段名 类型名;
--  删除约束

③删除约束：只有unique\primary key有效

alter table 表名 drop constraint 约束名;

3、外键约束

一张表(子表)的值必须引用自另一张表(父表),并且被引用的父表的字段必须具备唯一性，子表中被外键约束的字段值必须来自父表或者为null
一般在创建表的外键约束时，需要先创建父表，再创建子表

create table 父表(
	字段a 类型 primary key/unique;
);
create table 子表(
	字段b 类型;
	...
	foreign key(字段b) references 父表(字段a);
);

也可以在后期添加外键，不建议，可能非法值已经存在

alter table 子表 add foreign key(子表字段) references 父表(父表字段);

注意：应该先插入父表数据，再插入子表数据

/*父表更新、删除数据：
        1、先修改\删除子表，再删除父表
        2、设置级联删除、级联更新
            on delete cascade 级联删除
                父表数据删除，子表对应数据也随之删除
            on update cascade 级联更新
                父表数据更新，子表对应数据也随之更新*/
        
        create table 子表(
            ...
            foreign key(字段b) references 父表(字段a) on delete/update cascade;
        );