Oracle - CHECK Constraint - Only allow column to be changed if value in different column is not null

Question

So I have the following table:

CREATE TABLE Projects (
ID  INTEGER  CONSTRAINT ProjPK PRIMARY KEY, 
Column1 VARCHAR2(30) NOT NULL ,
Column2 VARCHAR2(10) NOT NULL ,
Column3 INTEGER NULL ,
Column4 VARCHAR2(20) NULL ,
Column5 INTEGER NOT NULL ,
Column6 INTEGER NULL ,
Column7 DATE NULL
);

I need to come up with a CHECK-Constraint for Column7 that allows Column7 only to be changed to a different value if columns 4 and 6 are NOT NULL.

I would like to express that the column7 must be NULL if columns 4 or 6 are NULL, respectively only can be NOT NULL if columns 4 AND 6 are NOT NULL. I hope that makes sense. Any help would be appreciated.

Answer 1

Use a simple check constraint that defines your restriction

alter table projects add CONSTRAINT check_null 
CHECK ( (column4 is  null and column6 is null and column7 is null) or 
        (column4 is not null and column6 is not null and column7 is not null) );

Some Test

Fails as column7 defined and columns 4,6 not

 INSERT INTO projects (id, column1, column7)  VALUES (1, 'fail', SYSDATE);
 --ORA-02290: check constraint (xxx.CHECK_NULL) violated

OK as all columns are NULL

  INSERT INTO projects (id, column1, column7)  VALUES (1, 'OK', null);
-- 1 row inserted. 

.. or all defined.

  INSERT INTO projects (id, column1,column4, column6, column7)  VALUES (2, 'OK','x',1, SYSDATE);
-- 1 row inserted. 

Note never use a trigger if a simple CHECK constraint is sufficient.