CODEWITHSUNDEEP
sqloracle-databasecreate-tablecheck-constraints
خالد بشير
خالد بشير

Reputation: 29

oracle column multiple check enum

How can I add a multi-list column in oracle table
like this

CREATE TABLE atable (
col1 varchar2(10) CONSTRAINT cons_atable_col1 CHECK (col1 IN ('a','b'))
col2 varchar2(10) case col1 
 WHEN 'a' THEN
        CONSTRAINT cons_atable_col2 CHECK (col2 IN ('c','d'))
    WHEN 'b' THEN
          CONSTRAINT cons_atable_col2 CHECK (col2 IN ('e','f'))
  
     ELSE 
null
end

);

Upvotes: 0

Views: 80

Answers (1)

GMB
GMB

Reputation: 222512

You can describe the constraint with boolean logic rather than with a case expression.

I think this implements the logic you want:

CREATE TABLE atable (
    col1 varchar2(10),
    col2 varchar2(10),
    CHECK ( (col1 = 'a' AND col2 IN ('c','d'))  OR (col1 = 'b' AND col2 IN ('e','f')) )
);

We could also express the check constraint as follows:

CHECK ((col1, col2) IN (('a', 'b'), ('a', 'd'), ('b', 'e'), ('b', 'f')))

Upvotes: 1

Related Questions