CODEWITHSUNDEEP
sqloracle-databasenullnotnull
user9832745
user9832745

Reputation:

If one value is NULL then both values are NULL

EDITED FOR CLARITY

First Part I would like to Have the data automatically changed according to the rule:

For each zone, either both the Area_one and Area_two must be specified, or neither of them are specified ex: either they are both NULL, or neither of them are NULL - Must be table level.

So far I have came up with:

constraint chk_Null check (Area_one is not null and Area_two is not null)

But with this the script just denies any inserted data that is null.

Second part I would like to automatically change the data according to rule:

For a given Value, when Value_one is present, the Value_two must be NULL (vice versa)

I have written:

constraint Value_chk check((Value_one IS NULL and Value_two IS NOT NULL) 
 or (Value_one is NOT NULL and value_two IS NULL))

But I am not sure how to finish these off, or if i'm in the right direction.

Thanks all in advanced!

Upvotes: 0

Views: 172

Answers (1)

EzLo
EzLo

Reputation: 14199

Both null or neither:

constraint chk_Null check (
    (Area_one is not null and Area_two is not null) OR
    (Area_one is null and Area_two is null)
    )

To change the data you will have to do it in a trigger, as your regular check constraint will validate data (and might roll it back) but not update it. Your trigger will have to be on insert and update and consider the case when supplied values are both not null (which one will you update?).

Upvotes: 1

Related Questions