CODEWITHSUNDEEP
oracle-databasewhere-clausecheck-constraint
Politiepet
Politiepet

Reputation: 93

Using where clause in Check

I have a situation where a field can be NULL when another field is certain values and for others it should be NOT NULL.

"Type"              VARCHAR2(30)      NOT NULL,
BestandLocatie      VARCHAR2(150)     NULL,

I made two constraints, the first one makes sure that only certain values in "Type" can be entered.

CONSTRAINT TypeCheck
          CHECK ("Type" IN ('Tab', 'Bass Tab', 'Chords', 'Power Tab', 'Guitar Pro', 
                    'Video Lesson', 'Drum Tab', 'Ukulele Chords')),

The other constraint (which gives an error, missing right parenthesis) should make sure that BestandLocatie is NOT NULL when "Type" is certain types:

CONSTRAINT BestandLocatieCheck 
    CHECK (BestandLocatie IS NOT NULL WHERE ("Type" IN ('Power Tab', 'Guitar Pro'
                            'Video Lesson')))

When I searched for the Where clause I only found examples of it in select statements. Does this mean that I can't use it here, is there an other method of doing this, or do I have to check this in the end application or can it only be done in PLSQL?

Upvotes: 2

Views: 2478

Answers (1)

Mat
Mat

Reputation: 206699

You can do something like this:

alter table foo add (constraint check_b 
  check ( (a in ('a', 'b') and b is not null)
       or (a not in ('a', 'b') /* and b is null */)
        )
);

The commented and b is null should be there depending on whether you want to require the value to be null in the other cases or not.

Demo:

SQL> create table foo (a varchar(2) not null, b varchar(2));

SQL> alter table foo add (constraint check_b check (
  (a in ('a', 'b') and b is not null) or (a not in ('a', 'b') and b is null))
);

Table altered.

SQL> insert into foo values ('a', 'b');

1 row created.

SQL> insert into foo values ('a', null);
insert into foo values ('a', null)
*
ERROR at line 1:
ORA-02290: check constraint (MAT.CHECK_B) violated


SQL> insert into foo values ('c', null);

1 row created.

SQL> insert into foo values ('c', 'b');
insert into foo values ('c', 'b')
*
ERROR at line 1:
ORA-02290: check constraint (MAT.CHECK_B) violated

Upvotes: 3

Related Questions