How can I add a constraint like a column has specific value when another column is null or not null?

Question

Here are my seed data rows:

{ organization_id: 1, parent_id: null, organization_type: OrganizationType.ORG },
{ organization_id: 2, parent_id: 1, organization_type: OrganizationType.CLIENT },
{ organization_id: 3, parent_id: 1, organization_type: OrganizationType.CLIENT }

I want to add a constraint to organizations table.

This constraint has these rules:

1. organization_type should be OrganizationType.ORG, when parent_id is null

2. organization_type should be OrganizationType.CLIENT, when parent_id is not null

How can I do this? thanks.

Answer 1

You can use a check constraint:

create table organizationns
(
  <other columns>, 
  parent_id integer,
  organization_type varchar(50) not null,
  constraint check_org_type 
    check (   (parent_id is null     and organization_type = 'ORG')
           or (parent_id is not null and organization_type = 'CLIENT')) 
);

---

But the organization_type column seems rather useless in that case, as you can always derive it from the parent_id (e.g. through a view)