数据库约束

数据库约束

约束概述

约束其实就是一种限制,用于修饰表中的列. 通过这种限制来保证表中数据的正确性、有效性和完整性。

约束的类型

约束名	约束关键字
主键	primary key
非空	not null
唯一	unique
外键	foreign key … references
默认	default

1主键约束(重点)

1.1 主键的作用

用来标注一条记录的唯一性，每个表都应该有一个主键，并且每个表只能有一个主键。
有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据

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

1.2 创建主键

主键：PRIMARY KEY
主键的特点：

• 主键字段值唯一(唯一不可重复)
• 主键列不能包含NULL值
• 一个表中只能有一个主键,但主键可以是多个字段 (联合主键)

创建主键方式：

方式1:创建表时,添加主键约束 (字段约束区)
	格式:
		create table 表名 ( 
			字段名称1 字段类型 字段约束区, 	
			字段名称2 字段类型 字段约束区
		);

方式2:创建表时,在约束区域添加约束(扩展)
	格式1:单一主键
		create table 表名 (
	    	字段名称1 字段类型, 	
	    	字段名称2 字段类型, 	
	    	primary key(字段名称1)  
		);

	格式2:联合主键
		create table 表名 (
	    	字段名称1 字段类型, 	
	    	字段名称2 字段类型, 	
	    	primary key(字段名称,字段名称1)  
		);

方式3:给已有表中添加主键(扩展)
	alter table 表名 add primary key(字段名称);

创建表用户表, 包含字段(id, name)将id做为主键

-- 主键约束
-- 方式1: 建表时在字段的约束区添加主键约束
CREATE TABLE user1(
	id INT PRIMARY KEY, # 在字段的约束区添加主键约束
	`name` VARCHAR(20)
);
-- 添加数据信息
INSERT INTO user1 VALUES(1,'tom'); -- 正常插入
INSERT INTO user1 VALUES(NULL,'tom'); -- 插入异常(主键不可为空): Column 'id' cannot be null
INSERT INTO user1 VALUES(1,'tom'); -- 插入异常(主键不可重复): Duplicate entry '1' for key 'PRIMARY'

-- 方式2: 建表时在约束区添加主键约束
CREATE TABLE user2(
	id INT, 
	`name` VARCHAR(20),
	PRIMARY KEY (id)
);
-- 方式3: 创建完表后,通过修改表结构添加主键约束
CREATE TABLE user3(
	id INT, 
	`name` VARCHAR(20)
);
-- 修改表添加主键
ALTER TABLE user3 ADD PRIMARY KEY (id);

创建表用户表, 包含字段(id, name) 将id和name做为联合主键

-- 联合主键
CREATE TABLE user4(
	id INT, 
	`name` VARCHAR(20),
	PRIMARY KEY (id,`name`)
);
-- 保证id和name字段不为空,然后联合判断唯一性
INSERT INTO user4 VALUES(1,'tom');	-- 正常插入
INSERT INTO user4 VALUES(2,"tom");	-- 正常插入
INSERT INTO user4 VALUES(1,"rose");	-- 正常插入
INSERT INTO user4 VALUES(2,"rose");	-- 正常插入
INSERT INTO user4 VALUES(1,"tom");	-- 插入异常:Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO user4 VALUES(NULL,"jack"); -- 插入异常:Column 'id' cannot be null
INSERT INTO user4 VALUES(3,NULL); -- 插入异常:Column 'name' cannot be null

注意 : 一张表中只有一个主键 , 主键可以为多个字段 , 不过我们一般增加一个字段 id 来作为主键.

1.3 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

具体操作:

• 删除user1表的主键

ALTER TABLE user1 DROP PRIMARY KEY;

1.4 主键自增

​ 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型是整型数字)

具体操作:

• 创建学生表user5, 包含字段(id, name)将id做为主键并自动增长

CREATE TABLE user5 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);

• 插入数据

-- 主键默认从1开始自动增长
INSERT INTO user5 (name) VALUES ('唐僧');
INSERT INTO user5 (name) VALUES ('孙悟空');
INSERT INTO user5 VALUES (null,'猪八戒');
INSERT INTO user5 VALUES (null,'沙僧');

DELETE和TRUNCATE的区别

DELETE删除表中的数据，不重置AUTO_INCREMENT的值
TRUNCATE摧毁表，重建表，AUTO_INCREMENT重置为1
如果想自定义id的值,可以使用下面的sql进行设置,一般不需要
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

2 唯一约束

在这张表中这个字段的值不能重复

2.1 唯一约束的基本格式

字段名 字段类型 UNIQUE

2.2 实现唯一约束

具体操作:

• 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

CREATE TABLE st7 (
	id INT,
	NAME VARCHAR(20) UNIQUE
);

• 添加一个学生

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

3 非空约束

被修饰的这个字段必须设置值,不能是NULL

字段名 字段类型 NOT NULL

具体操作:

• 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

• 添加一条完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

4 默认值

往表中添加数据时,如果不指定这个字段的数据,就使用默认值

默认值格式

字段名 字段类型 DEFAULT 默认值

具体操作:

• 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

• 添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

• 添加一条记录,不使用默认地址

INSERT INTO st9 VALUES (2, '张学友', '香港');

5 外键约束

5.1 什么是外键约束

一张表中的某个字段引用另一个表的主键
主表： 约束别人
副表/从表： 使用别人的数据，被别人约束

5.2 创建外键

1. 新建表时增加外键：
   [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
   关键字解释：
   CONSTRAINT – 约束关键字
   FOREIGN KEY(外键字段名) –- 某个字段作为外键
   REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段
2. 已有表增加外键：
   ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
   以"新建表时添加外键"演示

-- 先创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 然后创建员工表,添加外键约束
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

• 正常添加数据

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

• 部门错误的数据添加失败

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

5.3 删除外键(了解)

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

具体操作:

• 删除employee表的emp_depid_ref_dep_id_fk外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

• 在employee表情存在况下添加外键

ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

5.4 数据操作注意事项

添加数据时:
	先添加主表中的数据,再添加从表中的数据
删除数据时:
	先删从表中的数据,再删主表中的数据
修改数据时:
	如果主表中的主键被从表引用了,不能修改此主键的值