Setting value of row to NULL if another value is not present in Oracle

Question

I am using an Oracle database and trying to add a constraint that sets the chemical_value to NULL if there is a chemical_outlier present vice versa. I have been searching the Oracle docs for a possible solution but stuck

Create table chemical
(
chemical_id char(3) not null,
chemical_name varchar2(50) not null,
chemical_value numeric,
checmical_outlier varchar(50),

constraint checkChemical <DONT KNOW HOW TO APPROACH THIS>
)

Just need some kind of direction is approaching this. I know NULL values can be bad but just want them there instead of an empty row

A check constraint prevents an invalid value being entered, it doesn't change anything. You probably want a trigger, or else some logic in whatever inserts data. (By the way I would [make that `char` column a standard `varchar2`.](https://stackoverflow.com/a/42165653/230471) — William Robertson, Feb 01 '18 at 17:11
What are the being set to now? Empty strings? I assume both can't have data at once based on that condition. — Aaron Dietz, Feb 01 '18 at 17:22
Can you handle it on the insert? Just `NULLIF(chemical_value,0)` and `NULLIF(chemical_outlier,'')` — Aaron Dietz, Feb 01 '18 at 17:28
If someone / some process tries to insert a record with both chemical_value and checmical_outlier, you want to make chemical_value null because of checmical_outlier and checmical_outlier because of chemical_value. You'll end up with both values null. — Thorsten Kettner, Feb 01 '18 at 17:32
I'd suggest a check constraint that ensures that always exactly one of the two values is set. (This seems to be what you want.) So if some process or person tries to insert an invalid record this doesn't get silently converted into something that person / process is not aware of, but results in an error. — Thorsten Kettner, Feb 01 '18 at 17:34
@user9251416: See Alessandro Melo's constraint (and then stop reading :-) — Thorsten Kettner, Feb 01 '18 at 18:17
You had mutilated your request. I've rolled it back. If you have another question, then open another request. — Thorsten Kettner, Feb 01 '18 at 18:24

William Robertson · Answer 1 · 2018-02-01T17:37:34.673

A check constraint won't set anything to null, but it will prevent invalid values being entered:

create table chemical
( chemical_id       varchar2(3) not null
, chemical_name     varchar2(50) not null
, chemical_value    numeric
, chemical_outlier  varchar(50)
, constraint chem_value_or_outlier_chk
      check (not (chemical_value is not null and chemical_outlier is not null)
             and (chemical_value is not null or chemical_outlier is not null))
);

Now these fail:

insert into chemical (chemical_id, chemical_name, chemical_value, chemical_outlier)
values ('x', 'xyz', 123, 'outlier');

ORA-02290: check constraint (XXX.CHEM_VALUE_OR_OUTLIER_CHK) violated

insert into chemical (chemical_id, chemical_name, chemical_value, chemical_outlier)
values ('x', 'xyz', null, null);

ORA-02290: check constraint (XXX.CHEM_VALUE_OR_OUTLIER_CHK) violated

But these succeed:

insert into chemical (chemical_id, chemical_name, chemical_value, chemical_outlier)
values ('x', 'xyz', null, 'outlier');

insert into chemical (chemical_id, chemical_name, chemical_value, chemical_outlier)
values ('x', 'xyz', 123, null);

score 0 · Answer 2 · answered Feb 01 '18 at 17:51

If you call an INSERT without a column, that column is set with NULL by default. So, if you can control the INSERT's, just call them with 3 columns, the 2 mandatory and 1 of the other ones. In this case you need a check to guarante that anyone will call the INSERT with the 4 columns like that:

CONSTRAINT "CONSTRAINT_NAME"
CHECK ((chemical_value IS NULL AND checmical_outlier IS NOT NULL)
OR (chemical_value IS NOT NULL AND checmical_outlier IS NULL)) ENABLE

So, if someone try to define both columns, the constraint don't accept the INSERT.

But, if you have a form or something else that generate the INSERT query, and even when you don't fill a column, the INSERT is generated with that column with value "" (empty string), you need a trigger to convert this to a NULL value like that:

CREATE OR REPLACE TRIGGER "TRIGGER_NAME"
BEFORE INSERT OR UPDATE ON chemical
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    IF :new.chemical_value = ''
    THEN :new.chemical_value := NULL;
    END IF;
    IF :new.checmical_outlier = ''
    THEN :new.checmical_outlier := NULL;
    END IF;
END;

Please note that this trigger don't check the condition "one is filled and the other one isn't", the trigger just convert the empty string to NULL. You can use both, trigger and constraint, to guarante the condition and the conversion.

In Oracle an empty string is null. This violates the SQL standard, but is actually very handy and makes working with strings usually easier than in other DBMS, because the distinction of '' and null is quite error-prone. The suggested constraint, however seems to be exactly what's wanted here: allow only inserts where exactly one of the two values is set. — Thorsten Kettner, Feb 01 '18 at 18:15
That is true! I never noticed that before, thanks. I will let my trigger solution just because it make sense with other databases and can help someone in the future with similar question. — Alessandro Melo, Feb 01 '18 at 18:35

Setting value of row to NULL if another value is not present in Oracle

2 Answers2