Oracle19C入门到熟练013-列约束

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

熟练掌握Oracle数据库表设计中列的设计

约束定义

约束是强加在表上的规则或条件。确保数据库满足业务规则。保证数据的完整性。当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。约束可以是列一级别的 也可以是表级别的。定义约束时没有给出约束的名字，ORACE系统将为该约束自动生成一个名字，其格式为SYS_Cn，其中n为自然数(强烈建议各位在创建表或增加约束时，给约束定义名称)。

约束功能

约束的功能：实现一些业务规则，防止无效的垃圾数据进入数据库，维护数据库的完整性(完整性指正确性与一致性)。从而使数据库的开发和维护都更加容易。

约束分类

数据库约束有6种：

• 非空约束（NOT NULL)

• 唯一性约束（UNIQUE)

• 主键约束（PRIMARY KEY）

• 外键约束（FOREIGN KEY)

• 检查约束（CHECK)

• REF约束（REF)[不讲]

约束命名规范

非空约束 　　　　NN_表名_列名
唯一约束 　　　　UK_表名_列名
主键约束 　　　　PK_表名
外键约束 　　　　FK_表名_列名
条件约束 　　　　CK_表名_列名
默认约束 　　　　DF_表名_列名

非空约束（NOT NULL)

非空约束（NOT NULL）：顾名思义，所约束的列不能为NULL值。否则就会报错

操作方式1：建表时，明确指定列非空

-- 建表
create table T_TEST(
	ID NUMBER,
    NAME VARCHAR2(10) NOT NULL
);
-- 添加空值测试
insert into t_test(id, name) values(1, null);

操作方式2：使用alter 方式添加

-- 删表，再建表
drop table T_TEST;

-- 此时表name字段并没有非空约束
create table T_TEST(
	ID NUMBER,
    NAME VARCHAR2(10)
);
-- 添加非空约束
alter table t_test modify name VARCHAR2(10) not null;
-- 添加空值测试
-- ORA-01400: 无法将 NULL 插入 ("SCOTT"."T_TEST"."NAME")
insert into t_test(id, name) values(1, null); 

操作方式3：PL/SQL developer 可视化创建

 手动指定约束名，约束类型primary，约束的列

外键约束（FOREIGN KEY）

外键约束（FOREIGN KEY）：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性.

操作方式1：建表时，明确指定外键列

-- 建表
create table T_TEST4(
	ID NUMBER,
    DEPTNO NUMBER,
    CONSTRAINT FK_TEST_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
-- 插入数据，外键引向部门的编号
select deptno form dept;  -- 查看部门编号
insert into t_test4(id, deptno) values(1, 10);   -- 部门表10编号存在

-- ORA-02291: 违反完整约束条件 (SCOTT.FK_TEST_DEPTNO) - 未找到父项关键字
insert into t_test4(id, deptno) values(1, 100);  -- 部门表100编号不存在

操作方式2：使用alter 方式添加

-- 删表，再建表
drop table T_TEST4;

-- 此时表id字段并没有唯一约束
create table T_TEST4(
	ID NUMBER,
   	DEPTNO NUMBER
);
-- 添加外键
alter table t_test4 add constraint FK_TEST_DEPTNO foreign key(DEPTNO) REFERENCES DEPT(DEPTNO);

-- 插入数据，外键引向部门的编号
select deptno form dept;  -- 查看部门编号
insert into t_test4(id, deptno) values(1, 10);   -- 部门表10编号存在

-- ORA-02291: 违反完整约束条件 (SCOTT.FK_TEST_DEPTNO) - 未找到父项关键字
insert into t_test4(id, deptno) values(1, 100);  -- 部门表100编号不存在

操作方式3：PL/SQL developer 可视化创建

手动指定约束名，约束类型foreign，约束的列，参照表DEPT，参照列DEPTNO

注意：外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。

条件约束（CHECK）

条件约束（CHECK）：条件约束可用来实施一些简单的规则，比如列值必须在某个范围内

操作方式1：建表时，明确指定外键列

-- 建表
create table T_TEST5(
	ID NUMBER,
    SEX VARCHAR2(10),
    CONSTRAINT SEX_CHECK CHECK (SEX IN ('man', 'woman'))
);
-- 插入数据
insert into t_test5(id, sex) values(1, 'man');   -- 符合 IN ('man', 'woman') 条件
-- ORA-02290: 违反检查约束条件 (SCOTT.SEX_CHECK)
insert into t_test5(id, sex) values(1, 'dafei'); -- 不符合IN ('man', 'woman') 条件

操作方式2：使用alter 方式添加

-- 删表，再建表
drop table T_TEST5;

-- 此时表id字段并没有唯一约束
create table T_TEST5(
	ID NUMBER,
    SEX VARCHAR2(10)
);
-- 添加外键
alter table t_test5 add constraint SEX_CHECK check (SEX IN ('man', 'woman'));


-- 插入数据
insert into t_test5(id, sex) values(1, 'man');   -- 符合 IN ('man', 'woman') 条件
-- ORA-02290: 违反检查约束条件 (SCOTT.SEX_CHECK)
insert into t_test5(id, sex) values(1, 'dafei'); -- 不符合IN ('man', 'woman') 条件

操作方式3：PL/SQL developer 可视化创建

手动指定name跟条件

REF约束（REF)

官方给定义：不理解，不讲

REF约束的定义：REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

约束操作

禁用约束

ALTER TABLE 表名 DISABLE CONSTRAINT 约束名

启用约束

ALTER TABLE 表名 ENABLE CONSTRAINT 约束名

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名

开发建议

很多书本跟大厂都建议除了Primary key 主键之外都不加其他相关约束，数据的安全都交给客户端维护(比如：java程序校验等)。我个人持有的看法，如果不是高并发，大数据类型的项目，建议还是加上相关约束。目的：防菜鸟，提高数据安全性，合法性。