ORACLE约束以允许Null值，IF [英] ORACLE Constraint to allow Null Value, IF

问题描述

我是第一次使用oracle，我有一个名为ExpenseReport的表，该表存储有关费用的详细信息.

I'm working with oracle for the first time, and I have a table called ExpenseReport which stores details about expenses.

如果ERStatus ='PENDING'，我希望ApprUserNo允许为Null 任何帮助将不胜感激，我一直在努力整天整理

I want Nulls to be allowed for ApprUserNo IF ERStatus = 'PENDING' Any help would be greatly appreciated, I've been trying to sort this all morning

CREATE TABLE ExpenseReport 
(
  ERNo           NUMBER(10) NOT NULL,
  ERDesc         VARCHAR2(255) NOT NULL,
  ERSubmitDate   DATE NOT NULL,
  ERStatusDate   DATE NOT NULL,
  ERStatus       VARCHAR2(8) DEFAULT 'PENDING' NOT NULL,
  SubmitUserNo   NUMBER(10) NOT NULL,
  ApprUserNo     NUMBER(10) NOT NULL CONSTRAINT BEN_Check CHECK (ERStatus LIKE('PENDING')),
  UsersUserNo    NUMBER(10) NOT NULL,
  AssetAssetNo   NUMBER(10) NOT NULL,
  PRIMARY KEY (ERNo),
  CONSTRAINT Check_ER_Date CHECK (ERStatusDate >= ERSubmitDate),
  CONSTRAINT ERStatus_Null_Exception CHECK (IF ERStatus = 'PENDING',AppUserNo = NULLABLE),
  CONSTRAINT ERStatus_Option CHECK (ERStatus = 'PENDING','APPROBED','DENIED')
)

推荐答案

您需要重新形成约束.

首先，如果您希望某个字段保留空值，则无论其他规则如何，该字段都必须为可空字段.

First, if you ever want a field to hold nulls, regardless of any other rules, it must be a nullable field.

ApprUserNo     NUMBER(10)   NULL,   -- No Check Constrain

第二，创建一个约束，说ApprUserNo不能为NULL，除非ErStatus = 'PENDING'

Second, create a constraint that says ApprUserNo can not be NULL, unless ErStatus = 'PENDING'

CONSTRAINT ERStatus_Null_Exception CHECK (AppUserNo IS NOT NULL OR ERStatus = 'PENDING'),

这篇关于ORACLE约束以允许Null值，IF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！