Postgresql - Check constraint with conditions

Question

I am working check constraint into one table, I have status and status_sub_type columns, but for example my status 'Closed' only can be status_subtype = 'Closed-typeA' or Closed-typeB', but right now I just put available status_subtypes into my check constraint, How I can put a condition to say that exists 5 subtypes but if it is status Closed, only can insert 2 different subtypes?

if status = 'close' 
   then status_subtype in ('closed-typeA', 'closed-typeB')
else if status = 'open' 
   then status_subtype in ('open-typeA', 'open-typeB')
else if status = 'Active' 
   then status_subtype = ''


| Status | Subtype      |
|---------------------- |
| Closed | Closed-TypeA |
| Closed | Closed-TypeB |
| Open   | Open-TypeA.  |
| Open   | Open-TypeB.  |
| Active |              |

Here is my code

CREATE TABLE IF NOT EXISTS Core.person 
(
    person_id serial PRIMARY KEY NOT NULL,
    person_name varchar(200),
    status varchar(25) 
        CHECK (status IN ('Closed','Open','Active')),
    status_subtype varchar(25) 
        CHECK (status_subtype IN ('Closed-TypeA', 'Closed-TypeB', 'Open-TypeA', 'Open-TypeB'))
)

forpas · Accepted Answer · 2022-04-27 17:58:32Z

In a normalized design you would deal with this requirement by creating 2 tables: one for all possible statuses and the other for the subtypes of the statuses and in the table person you would only have 1 foreign key referencing the subtype of the status.

For your design you can use a CASE expression:

CREATE TABLE IF NOT EXISTS person (
    person_id serial PRIMARY KEY NOT NULL,
    person_name varchar(200),
    status varchar(25) CHECK (status IN ('Closed','Open','Active')),
    status_subtype varchar(25) 
    CHECK(CASE status
            WHEN 'Closed' THEN status_subtype IN ('Closed-TypeA','Closed-TypeB')
            WHEN 'Open' THEN status_subtype IN ('Open-TypeA','Open-TypeB')
            WHEN 'Active' THEN status_subtype = ''
          END  
         )
);

See the demo.

Or:

CREATE TABLE IF NOT EXISTS person (
    person_id serial PRIMARY KEY NOT NULL,
    person_name varchar(200),
    status varchar(25) CHECK (status IN ('Closed','Open','Active')),
    status_subtype varchar(25) 
    CHECK(
          (status= 'Closed' AND status_subtype IN ('Closed-TypeA','Closed-TypeB'))
          OR
          (status= 'Open' AND status_subtype IN ('Open-TypeA','Open-TypeB'))
          OR
          (status= 'Active' AND status_subtype = '')
         )
);

See the demo.

Collectives™ on Stack Overflow

Postgresql - Check constraint with conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related