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

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.

Regards

CodePudding user response：

That's task for a trigger, I'd say.

Simplified table:

SQL> CREATE TABLE projects
  2  (
  3     id        INTEGER CONSTRAINT projpk PRIMARY KEY,
  4     column1   VARCHAR2 (30),
  5     column2   VARCHAR2 (10),
  6     column3   INTEGER,
  7     column4   VARCHAR2 (20),
  8     column5   INTEGER,
  9     column6   INTEGER,
 10     column7   DATE
 11  );

Table created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER trg_ch7
  2     BEFORE UPDATE
  3     ON projects
  4     FOR EACH ROW
  5  BEGIN
  6     IF :old.column7 <> :new.column7
  7     THEN
  8        IF     :new.column4 IS NULL
  9           AND :new.column6 IS NULL
 10        THEN
 11           NULL;
 12        ELSE
 13           raise_application_error (
 14              -20000,
 15              'Col 7 can not be changed because cols 4 and 6 are not empty');
 16        END IF;
 17     END IF;
 18  END;
 19  /

Trigger created.

SQL>

Testing:

SQL> INSERT INTO projects (id, column1, column7)
  2       VALUES (1, 'test', SYSDATE);

1 row created.

SQL>
SQL> UPDATE projects
  2     SET column7 = SYSDATE   1
  3   WHERE id = 1;

1 row updated.

SQL>
SQL> UPDATE projects
  2     SET column4 = 'not empty'
  3   WHERE id = 1;

1 row updated.

SQL>
SQL> UPDATE projects
  2     SET column7 = SYSDATE   2
  3   WHERE id = 1;
UPDATE projects
       *
ERROR at line 1:
ORA-20000: Col 7 can not be changed because cols
4 and 6 are not empty
ORA-06512: at "SCOTT.TRG_CH7", line 9
ORA-04088: error during execution of trigger
'SCOTT.TRG_CH7'


SQL>

CodePudding user response：

Use a simple check constraint that defines your restriction

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

Some Test

Fails as column7 defined and columns 4,5 not

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

OK as all columns NULL

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

.. or all defined.

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

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