深入Mysql-03-MySQL 表的约束与数据库设计

数据库约束的概述

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。

约束种类

主键约束

用来唯一标识数据库中的每一条记录

• 主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

创建主键：

1. 在创建表的时候给字段添加主键
   字段名 字段类型 PRIMARY KEY
2. 在已有表中添加主键
   ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

代码示例：

create table stu (id int primary key,name varchar(20),age int
)

删除主键约束：
alter table stu drop primary key;


主键自增:
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
将stu表中的主键进行添加操作：
alter table stu add primary key(id)

添加主键自增操作：
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
插入几条数据看看效果：

-- 插入数据
insert into stu (name,age) values ('小乔',18); 
insert into stu (name,age) values ('大乔',20);
-- 另一种写法
insert into stu values(null,'周瑜',35);

可以看到我们的主键id字段是逐渐递增的。

修改自增长的默认值起始值：

• 创建表时指定起始值

CREATE TABLE 表名(列 名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;

-- 指定起始值为 1000 
create table stu1 (id int primary key auto_increment, name varchar(20)
) auto_increment = 1000;insert into stu1 values (null, '孔明');
select * from stu1;

• 创建好以后修改起始值

ALTER TABLE 表名  AUTO_INCREMENT=起始值;

alter table stu1 auto_increment = 2000;
insert into stu1 values (null, '刘备');

注意： delete删除的记录对自增长没有影响，但是使用truncate删除记录时，自增长会重新开始。

唯一约束

表中某一列不能出现重复的值

字段名 字段类型 UNIQUE

• 创建一个stu2表

create table stu2 (id int,name varchar(20) unique
)

• 插入一条数据

insert into stu2 values (1, '张三');

• 插入一条同样的数据

insert into stu2 values (1, '张三');

出现了错误： Duplicate entry '张三' for key 'name'

非空约束

某一列不能为 null

字段名 字段类型  NOT NULL

• 创建一个stu3表

create table stu3(id int,name varchar(20) not null, gender char(1)
)

• 添加一个数据

insert into stu3 values (1,'张三疯','男');

• 添加一个name为null的数据

insert into stu3 values (2,null,'男');

出现了错误：Column 'name' cannot be null

默认值

字段名 字段类型  DEFAULT 默认值

• 创建一个stu4表

create table stu4 ( id int,name varchar(20),address varchar(20)	default '广州'
)

• 添加一条默认地址数据

insert into stu4 values (1, '李四', default); 

• 添加一个非默认地址的数据

insert into stu4 values (3, '李四光', '深圳');

外键约束

创建一个员工表emp

CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(30),age INT,dep_name VARCHAR(30),dep_location VARCHAR(30)
);-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); 
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳'); 
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); 
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

出现了大量冗余数据，解决方案就是进行分表

1. 创建一个部门表

create table department(id int primary key auto_increment,dep_name varchar(20),dep_location varchar(20)
);

2. 创建一个员工表

-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(id int primary key auto_increment,name varchar(20),age int,dep_id int	-- 外键对应主表的主键
)

3. 添加俩个部门

insert into department values(null, '研发部','广州'),(null, '销售部', '深圳'); 

4. 添加一些员工

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT	INTO	employee	(NAME,	age,	dep_id)	VALUES	('李四',	21,	1);
INSERT	INTO	employee	(NAME,	age,	dep_id)	VALUES	('王五',	20,	1);
INSERT	INTO	employee	(NAME,	age,	dep_id)	VALUES	('老王',	20,	2);
INSERT	INTO	employee	(NAME,	age,	dep_id)	VALUES	('大王',	22,	2);
INSERT	INTO	employee	(NAME,	age,	dep_id)	VALUES	('小王',	18,	2);

5. 查询员工和部门
   
   
   这里我们可以看到部门id对应有部门信息，但是这里我们可以查润一条当前部门表不存在的id数据，这就要通过外键约束来解决了。

• 创建表的时候添加外键： [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
• 已有表增加外键：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

这里我们已经创建了表，那么我们使用第二种方式来创建外键约束。

alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id)

这里可以看到当我们赋予外键约束的时候无法创建出部门表里面没有的id记录。

删除外键：

ALTER TABLE 从表  drop foreign key 外键名称;

-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

外键的级联:
重新添加外键约束：

alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);

当我们想改以下部门表的id时，会出现如图问题。

因此我们需要进行级联操作(在修改和删除主表的主键时，同时更新或删除副表的外键值)。

示例：

drop table employee;create table employee(
id int primary key auto_increment, name varchar(20),
age int,
dep_id int,	-- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade
)INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

这时，我们对部门表进行删除操作。

drop table department;

出现了错误，无法删除或更新父行：外键约束失败。
我们对部门表进行更新操作来检测级联更新是否成功。

update department set id=10 where id=1; 

这里我们可以看到所有的id等于1的都被修改为10了。
最后我们删除部门号为10的来检测我们的级联删除是否成功。

delete from department where id=10;

可以看到我们的级联删除约束也是成功的。

表与表之间的关系

数据库设计

1. 数据规范化
   建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
2. 三大范式
   目前关系数据库有六种范式: 第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

• 1NF: 第一范式每一列不可再拆分，称为原子性。
• 2NF: 第二范式就是在第一范式的基础上所有列完全依赖于主键列。
• 3NF: 任何非主列不得传递依赖于主键。

欢迎java热爱者了解文章，作者将会持续更新中，期待各位友友的关注和收藏,另外对编程感兴趣的友友们可以加以下群共同学习。群号：127871664