数据库中的约束,顾名思义即是对插入数据库中的数据进行限定,这么做的目的是为了保证数据的有效性和完整性。这样就大幅度地提高了数据库中数据的质量,节省了数据库的空间和调用数据的时间。
数据库常见六大约束如下:
1.主键约束
2.非空约束
3.自增长约束
4.非负约束
5.唯一约束
6.外键约束
数据的完整性是指数据的正确性和一致性,可以通过定义表时定义完整性约束,也可以通过规则,索引,触发器等。约束分为两类:行级和表级,处理机制是一样的。行级约束放在列后,表级约束放在表后,多个列共用的约束放在表后。
完整性约束是一种规则,不占用任何数据库空间。完整性约束存在数据字典中,在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的,当约束启用时,他增强了数据的完整性,否则,则反之,但约束始终存在于数据字典中。
约束 | 说明 |
---|---|
PRIMARY KEY | 主键约束 |
PRIMARY KEY AUTO_INCREMENT | 主键、自动增长 |
UNIQUE | 唯一约束 |
NOT NULL | 非空约束 |
FOREIGN KEY | 外键约束 |
FOREIGN KEY ON UPDATE CASCADE | 外键级联更新 |
FOREIGN KEY ON DELETE CASCADE | 外键级联删除 |
-- 标准语法 CREATE TABLE 表名( 列名 数据类型 PRIMARY KEY, 列名 数据类型, ... ); -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY -- 给id添加主键约束 ); -- 添加数据 INSERT INTO student VALUES (1),(2); -- 主键默认唯一,添加重复数据,会报错 INSERT INTO student VALUES (2); -- 主键默认非空,不能添加null的数据 INSERT INTO student VALUES (NULL); -- 查询student表 SELECT * FROM student; -- 查询student表详细 DESC student;
-- 标准语法 ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键 ALTER TABLE student DROP PRIMARY KEY;
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY; -- 添加主键 ALTER TABLE student MODIFY id INT PRIMARY KEY;
-- 标准语法 CREATE TABLE 表名( 列名 数据类型 PRIMARY KEY AUTO_INCREMENT, 列名 数据类型, ... ); -- 创建student2表 CREATE TABLE student2( id INT PRIMARY KEY AUTO_INCREMENT -- 给id添加主键自增约束 ); -- 添加数据 INSERT INTO student2 VALUES (1),(2); -- 添加null值,会自动增长 INSERT INTO student2 VALUES (NULL),(NULL); -- 查询student2表 SELECT * FROM student2; -- student2表详细 DESC student2;
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型; -- 删除自动增长 ALTER TABLE student2 MODIFY id INT;
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT; -- 添加自动增长 ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;
-- 标准语法 CREATE TABLE 表名( 列名 数据类型 UNIQUE, 列名 数据类型, ... ); -- 创建student3表 CREATE TABLE student3( id INT PRIMARY KEY AUTO_INCREMENT, tel VARCHAR(20) UNIQUE -- 给tel列添加唯一约束 ); -- 添加数据 INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666'); -- 添加重复数据,会报错 INSERT INTO student3 VALUES (NULL,'18666666666'); -- 查询student3数据表 SELECT * FROM student3; -- student3表详细 DESC student3;
-- 标准语法 ALTER TABLE 表名 DROP INDEX 列名; -- 删除唯一约束 ALTER TABLE student3 DROP INDEX tel;
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE; -- 添加唯一约束 ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;
-- 标准语法 CREATE TABLE 表名( 列名 数据类型 NOT NULL, 列名 数据类型, ... ); -- 创建student4表 CREATE TABLE student4( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL -- 给name添加非空约束 ); -- 添加数据 INSERT INTO student4 VALUES (NULL,'张三'),(NULL,'李四'); -- 添加null值,会报错 INSERT INTO student4 VALUES (NULL,NULL);
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型; -- 删除非空约束 ALTER TABLE student4 MODIFY NAME VARCHAR(20);
建表后单独添加非空约束
-- 标准语法 ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL; -- 添加非空约束 ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;
外键约束概念
建表时添加外键约束
-- 创建db2数据库 CREATE DATABASE db2; -- 使用db2数据库 USE db2; -- 创建user用户表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- id NAME VARCHAR(20) NOT NULL -- 姓名 ); -- 添加用户数据 INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五'); -- 创建orderlist订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, -- id number VARCHAR(20) NOT NULL, -- 订单编号 uid INT -- 订单所属用户 ); -- 添加订单数据 INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1), (NULL,'hm003',2),(NULL,'hm004',2), (NULL,'hm005',3),(NULL,'hm006',3); -- 添加一个订单,但是没有所属用户。这合理吗? INSERT INTO orderlist VALUES (NULL,'hm007',8); -- 删除王五这个用户,但是订单表中王五还有很多个订单呢。这合理吗? DELETE FROM USER WHERE NAME='王五'; -- 所以我们需要添加外键约束,让两张表产生关系
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
-- 创建user用户表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- id NAME VARCHAR(20) NOT NULL -- 姓名 ); -- 添加用户数据 INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五'); -- 创建orderlist订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, -- id number VARCHAR(20) NOT NULL, -- 订单编号 uid INT, -- 订单所属用户 CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 添加外键约束 ); -- 添加订单数据 INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1), (NULL,'hm003',2),(NULL,'hm004',2), (NULL,'hm005',3),(NULL,'hm006',3); -- 添加一个订单,但是没有所属用户。无法添加 INSERT INTO orderlist VALUES (NULL,'hm007',8); -- 删除王五这个用户,但是订单表中王五还有很多个订单呢。无法删除 DELETE FROM USER WHERE NAME='王五';
删除外键约束
-- 标准语法 ALTER TABLE 表名 DROP FOREIGN KEY 外键名; -- 删除外键 ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名); -- 添加外键约束 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
-- 添加外键约束,同时添加级联更新 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE; -- 添加外键约束,同时添加级联删除 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE; -- 添加外键约束,同时添加级联更新和级联删除 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE; -- 删除外键约束 ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1; -- 添加外键约束,同时添加级联更新和级联删除 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE; -- 将王五用户的id修改为5 订单表中的uid也随之被修改 UPDATE USER SET id=5 WHERE id=3; -- 将王五用户删除 订单表中该用户所有订单也随之删除 DELETE FROM USER WHERE id=5;