在约束中使用函数和布尔运算符
create table invoice_check_others
(
invoice_id number,
invoice_name varchar2(20),
invoice_type int,
invoice_clerk varchar2(20),
invoice_total number(9,2) default 0,
payment_total number(9,2) default 0,
--发票总数必须在1~1000之间
constraint invoice_ck check (invoice_total between 1 and 1000),
--发票名称必须为大写字母
constraint check_invoice_name check (invoice_name = upper (invoice_name)),
--发票类别必须在1,2,3,4,5,6,7之间
constraint check_invoice_type check (invoice_type in (1,2,3,4,5,6,7)),
--发票处理员工号不能为null
constraint check_invoice_clerk check (invoice_clerk is not null)
)
在代码中,使用了between,is not null, upper等函数来创建检查约束, 想表中插入一条记录:
insert into invoice_check_others
values (1,'INVOICE_NAME',1,'b02393',1000,1000)
如果将上述语句中invoice_name改为小写字母,将引发约束违法异常
insert into invoice_check_others
values (1,'invoice_name',1,'b02393',1000,1000)
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件(APPS.CHECK_INVOICE_NAME)
使用检查约束有如下限制:
- 不能为视图指定检查约束,但是可以在视图上使用WITH CHECK OPTION 子句,该子句与检查约束等同。
- 检查约束不能包含子查询和标量子查询表达式,不能包含 CURRENT_DATE、CURRENT_TIMESTAMP、DBTIMEZONE、
LOCALTIMESTAMP、SESSIONTIMEZONE、SYSDATE、SYSTIMESTAMP、UID、USER和USERENV等函数。 - 检查约束中不能包含自定义函数 不能包含伪列,比如CURRVAL、NEXTVAL、LEVEL或ROWNUM
查看表约束
Oracle将用户创建的表、约束等信息都放在数据字典表中,允许开发人员查询数据字典或视图来获取数据库对象的信息。
数据字典主要包含以下内容
- 各种方案对象的定义信息,入表、视图、索引、同义词、存储过程、函数、包、触发器和各种对象
- 存储空间的分配信息,入为某个对象分配了多少存储空间,该对象使用了多少存储空间
- 安全信息,如账户、权限、角色、完整性约束信息
- 数据库实例运行时的性能和统计信息
- 其他数据库运行过程中的基本信息
数据字典根据其前缀可以分为4类:
- user:用户所创建对象对应的数据字典表,例如user_objects、user_tables等
- all:用户所能访问对象(包括用户创建的对象)对应的数据字典表,例如all_objects、all_tables
- dba:所有对象对应的数据字典表,例如all_objects、all_tables等
- v$:描述系统性能相关的数据字典表,例如通过v$version表可以
获得数据库版本信息。
有两个数据字典视图提供了约束的详细信息。 - user_constraints:对于表中的每一个约束,在该表中都有一条记录描述这个约束,该表包含约束应用到的表
- uer_cons_columns:视图中显示约束的字段名称。如果主键是一个联合(多字段)主键,这个是途中将有约束的两条记录,联合主键的每一个字段队医一条记录,每一条记录通过position(在联合主键中的位置)来区别。
例如要查询invoice_check_others表中的所有约束信息,可以使用如下的sql语句:
SELECT CONSTRAINT_NAME, SEARCH_CONDITION, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME =UPPER ('INVOICE_CHECK_OTHERS');
在user_constraints视图中比没有包含约束应用到的列信息,为了获取invoice_check_others表的列约束信息,可以使用如下代码查询user_cons_columns视图
--查询约束应用的列信息
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM
USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER ('INVOICE_CHECK_OTHERS');