常用的约束
约束的作用:
在设计表的时候使用约束可以帮助维护表中记录的完整性和有效性
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语句相对应)
- Cannot delete or update a parent row: a foreign key constraint fails (myDataBase.student, CONSTRAINT fk FOREIGN KEY (student_id) REFERENCES user (id))
- 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字节 |