数据库设计三大范式和五大约束

一、数据库设计三大范式 什么是范式：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库 中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须 满足一定的范式。 在实际开发中最为常见的设计范式有三个： 1．第一范式1NF(确保每列保持原子性) 每一列属性都是不可再分的属性值，确保每一列的原子性 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。 根据实际需求设计的字段值不可再分解。比如“部门”和“角色”在需求中都较常用，应该分开两个字段。 2．第二范式2NF(确保表中的每列都和主键相关)（拆多表） 第二范式是说一张表中包含了多种不同的实体属性，那么要必须分成多张表 一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。订单表和商品表不能设计 在一张表里，应该分开两个表，再加一个多对多的商品id和订单id关联表。（一个表只描述一件事情） 分析：上面第一张表满足第一范式，即每个字段不可再分，但是这张表设计得并不好，或者说，这张表的设 计并不满足第二范式。因为这张表里面描述了两件事情：商品信息、订单信息，“数量"完全依赖于"订单 id”、“商品名"与"价格"完全依赖于"商品id”。这么做的后果是： 1、数据冗余：同一个商品有n个订单，"数量"重复n-1次；同一个订单拥有了m个商品，"商品名"与"价格"重 复m-1次； 2、更新异常：若调整了某个商品“商品”，数据表中所有行的"价格"值都需要更新，否则会出现同一个商品 “价格”不同的情况； 3、插入异常：假设要加一个新商品，暂时没有订单，那么由于没有"订单id"关键字，"商品"与"价格"也无法 记录入数据库； 4、删除异常：假设一批商品已经完成了订单，这些订单记录就应该从数据库表中删除。但是，与此同 时，"商品名"与"价格"也被删除了，显然，这最终可能会导致插入异常。 所以，此表的结构必须修改，修改后如截图下面三种表：增加了表，将订单信息与商品信息通过一张中间表 关联，很好地解决了上面的几个问题，这就是第二范式的中心----保证一张表只讲一件事情。 3．第三范式3NF(在2NF的基础上加外键) 第三范式（3NF）：必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相 关，（表中的每一列只能依赖于主键）； 例如：订单表中需要有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户id即可（外键）， 而不能有其他的客户信息。因为其他的客户信息直接关联于用户id，而不是直接与订单id直接相关。 订单表中的用户信息不直接设计字段，设计一个用户id字段再外键关联用户表即可。 分析：第三范式和第二范式有点像，从截图第一张数据库表结构中可以看出，“商品id”、“商品名”、“价 格”、“数量"和主键"订单id"直接关联，但是"用户名”、“电话"却不直接和主键"订单id"相关联，和"电话"直接相 关联的是"用户id”，如果表结构这么设计，同样会造成和第二范式一样的数据冗余、更新异常、插入异常、删 除异常的问题。 总结： 第1范式（1NF）：原子性，字段不可再分,否则就不是关系数据库; 第2范式（2NF）：唯一性 ，一个表只说明一个事物/只描述一件事情; 第3范式（3NF）：每列都与主键有直接关系，用外键做表的关联，不存在传递依赖; 注意事项： 1.第二范式与第三范式的本质区别：在于有没有分出多张表。 第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多 张表的话，一张表中只能有另一张表的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张 表中查询）。 2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。 三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然 要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数 据库。 二、五大约束： 数据库中的五大约束包括： 1.主键约束（Primay Key Coustraint） 唯一性，非空性； 2.唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个； 3.默认约束 (Default Counstraint) 该数据的默认值； 4.外键约束 (Foreign Key Counstraint) 需要建立两表间的关系； 5.非空约束（Not Null Counstraint）:设置非空约束，该字段不能为空。 五大约束的语法示例： 1. 添加主键约束 Alter table 表名 add Constraint 主键名 primary key(字段) 2. 添加唯一约束 Alter table 表名 add Constraint 约束名 unique(字段) 3. 添加默认约束 Alter table 表名 add Constraint 约束名 default（默认内容） for 字段名 4. 添加检查约束 Alter table 表名 add Constraint 约束名 check (字段表达) 5. 添加外键约束 Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名（字段名） 详细介绍： （1）[外键约束 (Foreign Key Counstraint) ] 1.设置外键的注意事项： 　　①：只有INNODB的数据库引擎支持外键，修改my.ini文件设置default-storage-engine=INNODB； 　　②：外键与参照列的数据类型必须相同。（数值型要求长度和无符号都相同，字符串要求类型相同，长 度可以不同）； 　　③：设置外键的字段必须要有索引，如果没有索引，设置外键时会自动生成一个索引； 2.设置外键的语法： [CONSTRAINT 外键名] FOREIGN KEY(外键字段) REFERENCES 参照表(参照字段) [ON DELETE SET NULL ON UPDATE CASCADE] – 设置操作完整。 3、外键约束的参照操作： 当对参照表的参照字段进行删除或更新时，外键表中的外键如何应对。 参照操作可选值： 　　RESTRICT: 拒绝对参照字段的删除或修改（默认）； 　　NO ACTION:与RESTRICT相同，但这个指令只在MySql生效； 　　CASCADE: 删除或更新参照表的参照字段时，外键表的记录同步删除或更新； 　　SET NULL: 删除删除或更新参照表的参照字段时，外键表的外键设为NULL (此时外键不能设置为NOT NULL)。 （2）[主键约束]（Primay Key Coustraint） 1.主键的注意事项：主键默认非空，默认唯一性约束，只有主键可以设置自动增长（主键不一定自增，自增一 定是主键）。 2.设置主键的方式： 　　①：在定义列时设置：id INT UNSIGNED PRIMARY KEY。 　　②：在列定义完成后设置：PRIMARY KEY(id)。 总结： 1.primary KEY:设置主键约束； 2.UNIQUE：设置唯一性约束，不能有重复值； 3.DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2 4.NOT NULL：设置非空约束，该字段不能为空； 5.FOREIGN key :设置外键约束。