PSQL Constraint for Parent Children Relationship

[sandbox-apps]

Hi all @supabase users is there any way I can make a constraint to validate the integrity of a relationship?
I want the parent table to have atleast minimum of 2 children upon insert (Optionally can also specify max but im focused on the children minimum constraint)

Use Case:
Polls Table where you can create a Poll with atleast 2 options...
I can make a table with columns of:
optionA Text (required)
optionB Text (required)
optionC Text (optional)
optionD Text (optional)

This is ok, but with some limitation, as you can see you are limited to max of 4 options. Typical polling system can add more options.

How can I achieve this? I want to achieve this at db level so I can make use of supabase studio insert row functionality with validation... Thanks

[steve-chavez]

Hey @sandbox-apps,

Edit: an array was the solution here, see below.

For your case, I think you could create a trigger that enforces the constraint(does a raise foreign_key_violation inside).

Hard to come with a snippet without a sample schema, but there's an example here(see the check_role_exists() trigger function).

[sandbox-apps]

Well the schema would be something link this that involves two table

class Poll {
  id: string
  text: string
}

class Option {
  pollId: string
  id: string
  text: string
}

But I'll try your idea thanks @steve-chavez

[steve-chavez]

@sandbox-apps Hm, instead of a table couldn't you make options be an array? Then enforcing the presence of two polls would be a simple check constraint. Like:

create table polls(
  id text primary key,
  txt text,
  options text[] CHECK (cardinality(options) >= 2)
);

[sandbox-apps]

Thanks!!! This will be okay to. I can also limit by max 10 options here :D