ORACLE constraint for Null Value resolution if
This is my first time working with oracle and I have a table called ExpenseReport that stores expense details.
I want Nulls to resolve 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')
)
source to share
You need to re-shape your constraints.
First, if you ever want a field to contain zeros, regardless of any other rules, it must be a zero-valued field.
ApprUserNo NUMBER(10) NULL, -- No Check Constrain
Second, create a constraint where ApprUserNo cannot be NULL if ErStatus = 'PENDING'
CONSTRAINT ERStatus_Null_Exception CHECK (AppUserNo IS NOT NULL OR ERStatus = 'PENDING'),
source to share
This should do it:
CONSTRAINT ERStatus_Null_Exception
CHECK (ERStatus <> 'PENDING' AND AppUserNo IS NOT NULL)
You should also change: CONSTRAINT BEN_Check CHECK (ERStatus LIKE('PENDING'))
c CONSTRAINT BEN_Check CHECK (ERStatus = 'PENDING')
because a) LIKE is not a function and b) LIKE without wildcards is meaningless.
Then you have another error: CONSTRAINT ERStatus_Option CHECK (ERStatus = 'PENDING','APPROBED','DENIED')
it must beCONSTRAINT ERStatus_Option CHECK (ERStatus IN ('PENDING','APPROBED','DENIED'))
Please re-read the chapter in the Oracle manual that contains syntax for CREATE TABLE and SQL clauses.
source to share