mysql笔记五 数据库表的约束、级联操作

数据库表的约束

约束的作用

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

约束种类

primary key  # 主键
unique       # 唯一
not null     # 非空
foreign key  # 外键
check        # 检查约束 （mysql不支持）

PRIMARY KEY

主键的作用

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

哪个字段应该作为表的主键

通常不用业务字段作为主键，单独给每张表设计一个id，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

比如：身份证、学号不建议做成主键。

创建和删除主键

# 在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY

# 在已有的表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

# 在已有的表中删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

举例

# 创建学生表st，包含字段(id, name, age)将id作为主键

# 创建表的时候给字段添加主键
create table st(
	id int primary key,
	name varchar(20),
	age int
);

# 在已有的表中添加主键
create table st(
	id int,
	name varchar(20),
	age int
);
alter table st add primary key(id);


# 删除st表中的主键
alter table st drop primary key;

主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值。

# 表示自动增长(字段类型必须是整数类型)
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT 

举例：

# 在st表中插入新的学生信息
insert into st values(null, '张三', 18);
# id会自动增长

修改自增长的默认值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法。

# 创建表时指定自增长起始值
CREATE TABLE 表名(
	列名 int primary key AUTO_INCREMENT
)AUTO_INCREMENT=起始值;

# 创建好以后指定自增长起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

DELETE和TRUNCATE对自增长的影响

• DELETE删除所有的记录以后，自增长没有影响。
• TRUNCATE删除以后，自增长又重新开始。

UNIQUE

什么是唯一约束： 表中某一列不能出现重复的值。

唯一约束的格式

字段名 字段类型 UNIQUE;

举例：

# 创建学生表st，包含字段(id, name), name这一列设置唯一约束
create table st(
	id int,
	name varchar(20) unique
);
# 设置完以后，不能出现同名的学生

NOT NULL

什么是非空约束：某一列不能为null。

非空约束的语法格式

字段名 字段类型 NOT NULL

举例：

# 创建表学生表 st, 包含字段(id,name,gender)，其中 name 不能为 NULL
create table st (
	id int,
	name varchar(20) not null,
	gender char(1)
);
# 之后添加记录的时候，name字段不能为空

DEFAULT

什么是默认值：如果插入一条记录的某个字段为空，恰好该字段为DEFAULT，那么会使用先前设置的默认值。

默认值的语法格式

字段名 字段类型 DEFAULT 默认值

举例：

# 创建一个学生表 st，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
	 id int,
	 name varchar(20),
	 address varchar(20) default '广州'
);

主键与非空+唯一的区别

如果一个字段设置了非空与唯一约束，该字段与主键的区别是什么？

• 主键在一个表中只能有一个。主键可以单列，也可以多列。
• 自增长只能用在主键上。

FOREIGN KEY

什么是外键约束：
一张表的一个字段受限于另外一张表的一个字段对应的值。这里涉及到两张表：被引用的表叫主表（父表），另外一张叫从表（子表）。

优点：可以解决单表的数据冗余问题。

举例：

# 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
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, '销售部', '深圳');


# 以上数据表的缺点非常明显，就是数据冗余，后期还会出现增删改的问题

外键约束的基本语法

# 新建表时增加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

#已有表增加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
键字段名);

举例：

# 先建立主表：department表
create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);

#  创建从表 employee 并添加外键约束 emp_depid_fk
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)
);

# 添加 2 个部门
insert into department values(null, '研发部', '广州'), (null, '销售部', '深圳');

# 添加员工
insert into employee (name, age, dep_id) values ('张三', 20, 1);
# 这个员工就对应主表中的"研发部 广州"

# 如果插入不存在的部门，会提示错误。

删除外键

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

举例：

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

# 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);

级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

我们可以在创建数据表之前禁用所有的外键约束：

SET FOREIGN_KEY_CHECKS = 0;

也可以像下面一样，禁用某一个数据表的外键约束：

级联操作的语法

# 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新
ON UPDATE CASCADE

# 级联删除
ON DELETE CASCADE

举例：

# 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除
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
);

MySQL知识点列表

MySQL知识点列表