④常用的约束

常用的约束

约束的作用：

在设计表的时候使用约束可以帮助维护表中记录的完整性和有效性

1、主键

• 特点：非空且唯一，每个表只能有一个主键

CREATE TABLE `student`(
	`id` int PRIMARY KEY
);

2、非空

CREATE TABLE `student`(
	`id` int NOT NULL
);

3、唯一

• 列级约束用法

CREATE TABLE `student`(
	`id` int UNIQUE
);

• 表级约束用法

CREATE TABLE `student`(
	`id` int,
	`studentId` int,
	UNIQUE(`id`, `studentId`)
);

4、外键

外键的作用：
1.外键可以保证数据完整性和一致性，级联操作非常方便；
2.使用外键可以将数据完整性判断托付给数据库完成，减少程序的代码量；
外键的语法

-- 建表添加外键约束语法
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

--修改表添加外键约束语法
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

-- 删除外键
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

外键的工作方式

• 阻止执行
  ① 从表插入新行，其外键值不是主表的主键值便阻止插入；
  ② 从表修改外键值，新值不是主表的主键值便阻止修改；
  ③ 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
  ④ 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。
• 级联执行
  ① 主表删除行，连带从表的相关行一起删除。
  ② 主表修改主键值，连带从表相关行的外键值一起修改

外键的工作方式 - 实例演示

CREATE TABLE `user`(
	`id` int PRIMARY KEY,
	`name` char(50)
);

CREATE TABLE `student`(
	`student_id` int PRIMARY KEY,
	`class` int,
	CONSTRAINT `fk` FOREIGN KEY (`student_id`) REFERENCES `user`(`id`)
);

INSERT INTO `user`
VALUES	(1, "张三"),
		(2, "李四"),
		(3, "王五"),
		(4, "赵六");
			 
INSERT INTO `student`
VALUES	(1, 1),
		(2, 1),
		(3, 2),
		(4, 3);

• 此时两个表的数据已经被外键约束，执行以下操作：

 1. 操作主表中将被外键约束的数据
	UPDATE `user` SET `id` = 5 WHERE `name` = "张三";  -- SQL报错
	
 2. 操作从表，将被约束的数据修改为主表中<不存在>的外键数据 
	UPDATE `student` SET `student_id` = 5 WHERE `class` = 3;  -- SQL报错
	
 3. 操作从表，将被约束的数据修改为主表中<存在>的外键数据 
	UPDATE `student` SET `student_id` = 4 WHERE `class` = 2;  -- SQL语句通过

• SQL报错信息（序号与SQL语句相对应）

1. Cannot delete or update a parent row: a foreign key constraint fails (myDataBase.student, CONSTRAINT fk FOREIGN KEY (student_id) REFERENCES user (id))
2. Cannot add or update a child row: a foreign key constraint fails (myDataBase.student, CONSTRAINT fk FOREIGN KEY (student_id) REFERENCES user (id))

示例中的三条SQL演示了外键的工作方式中的第②、④点。

外键的缺点

• 并发问题。在使用外键的情况下，每次修改/删除表数据都需要去另外一个表检查数据，需要获得额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁；
• 扩展性问题。比如从"Mysql"将数据迁移到"Oracle"，外键依赖于数据库本身的特性，做迁移可能不方便；
• 不利于分库分表。在水平拆分和分库的情况下，外键是无法生效的。将数据库关系的维护放入应用程序中，可以为将来的分库分表省去很多的麻烦。

5、默认值

create table `student`(
	`id` int,
	`core` int DEFALUT 1 
);

6、检查约束（CHECK、CK）

检查约束指的是在数据列上设置一些过滤条件，当过滤条件满足的时候才可以进行保存，如果不满足则出现错误。例如：如果设置年龄的信息，年龄0~250，性别：男、女。

范例：设置检查约束

--删除数据表
DROP TABLE member PURGE;
--清空回收站
PURGE RECYCLEBIN;
--创建数据表
CREATE TABLE member（
	mid    NUMBER,
	name   VARCHAR2(20)  NOT NULL,
	age	NUMBER(3),
	CONSTRAINT pk_mid PRIMARY KEY (mid),
	CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 250)
）;
--测试数据
insert into member(mid,name,age)values (1,'韩信',200);
INSERT INTO member(mid,name,age)values (2,'李白',100); 
--提交事务
COMMIT;

范例：保存正确的数据

INSERT INTO member(mid,name,age) values (3,'典韦',34);

范例：保存错误的数据

insert into member (mid,name,age) values (4,'凯'，999);

tinyint	smallint	middleint	int	bigint
1字节	2字节	3字节	4字节	8字节