2

I am trying to add a check constraint on multiple columns in Oracle table that restricts user from inserting NULL into 3 columns simultaneously. However each column in the table can accept NULL independently but not 3 of the columns together. 

ALTER TABLE table1 ADD CONSTRAINT CK_not_null 
CHECK (col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL);

This check constraint is not allowing NULL in any of the three columns. Any thought on this?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
user3773317
  • 21
  • 1
  • 2

1 Answers1

2

This constraint will not achieve your needs - it checks that all three columns are not null. The behavior you're describing can be achieved by negating (with the not boolean operator) a condition where all three columns are null:

ALTER TABLE table1 
ADD CONSTRAINT 
ck_not_null CHECK 
(NOT (col1 IS NULL AND col2 IS NULL AND col3 IS NULL))
Mureinik
  • 297,002
  • 52
  • 306
  • 350