Oracle constraint check condition with null and not null

Question

My question is about checking constraint, the normal syntax is :

ALTER TABLE barracks
ADD CONSTRAINT chk_barracks CHECK( status IN('Destroyed', 'constructed'))

This means that column status should be destroyed OR constructed.

In the following I am specifying that barracks doesn't accept null values

 ALTER TABLE barracks
 ADD CONSTRAINT chk_barracks CHECK(status IN('Destroyed', 'constructed')
    AND status IS NOT NULL)

Now I added column colour. I want to specify if barracks is constructed, then colour should be RED only and the column STATUS should be NOT NULL

If BARRACKS IS DESTROYED

Then colour should be BLACK and the column STATUS should be NULL.

So here is the checking constraint that I wrote:

 ALTER TABLE barracks
 ADD CONSTRAINT chk_barracks CHECK( ((status IN ('Destroyed', 'constructed')
         AND status IS NOT NULL) 
         AND (color IN('RED') )) 
    OR (( status IN('Destroyed', 'constructed') 
         AND status IS NULL) 
         AND (color IN('BLACK') )))

The problem that if color is RED, I am still able to insert NULL into the column status. How to solve this constraint in a way it will check my conditions that I have mentioned above?

If BARRACKS IS DESTROYED Then colour should be BLACK And the coloumn should be NULL-- what column should be NULL ??? — schurik
– schurik, Commented Jun 21, 2013 at 15:25
it's not possible for the value of the column to be NULL and "Destroyed" at the same time. you have to choose either "Destroyed" or NULL :) — schurik
– schurik, Commented Jun 21, 2013 at 15:38
@schurik IF i wrote it in such way, would be better ? (( status IN('Destroyed', 'constructed') OR status IS NULL) — Moudiz
– Moudiz, Commented Jun 21, 2013 at 15:42
@schurik oh well it worked the solution is to replace and with or to be in this way: ALTER TABLE barracks ADD CONSTRAINT chk_barracks CHECK( (( status IN('Destroyed', 'constructed') AND status IS NOT NULL) AND (color IN('RED') )) OR (( status IN('Destroyed', 'constructed') >> OR status IS NULL) AND (color IN('BLACK') ))) — Moudiz
– Moudiz, Commented Jun 21, 2013 at 15:53

APC · Accepted Answer · 2013-06-21 15:48:32Z

3

It's a bad idea to use a CHECK constraint to enforce values like 'Destroyed', 'constructed'. Use a foreign key to a lookup table instead. Apart from anything else, that would prevent logical absurdities such as :

( status IN('Destroyed', 'constructed') 
         AND status IS NULL)

You then also have a simpler check condition:

( ( status is not null and color = 'RED') 
   or ( status is null and color = 'BLACK')

At the very least you should have two separate check constraints: one to validate the values of STATUS, and one to validate the relationhip between COLOR and whether STATUS is populated.

More on the foreign key. Create a table like this:

 create table barracks_statuses
     ( status varchar2 (15) not null
       , constraint bsts_pk primary key (status)
 organization index;

 insert into barracks_statuses values ( 'Destroyed');
 insert into barracks_statuses values ( 'constructed');

Then enforce a foreign key like this:

alter table barracks
    add constraint barr_bsts_fk foreign key (status)
        references (status);

Such an approach is useful because it gives more flexibility. You add a new status such as 'Planned' or fix the typo in 'constructed' just with DML, instead or rewriting some convoluted check constraint.

edited Jun 21, 2013 at 15:48

answered Jun 21, 2013 at 15:32

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Moudiz Over a year ago

APC What do you mean by :Use a foreign key to a lookup table instead? how a foreign can help me in my table ? and checking it ?

Moudiz Over a year ago

I have to specify what is the status and what is the color .

Collectives™ on Stack Overflow

Oracle constraint check condition with null and not null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related