How to specify relation on PostgreSQL table also has enum property set to specific value?

I am using kysely, a SQL generating tool for Node.js, to make the PostgreSQL queries. Right now I am working on the migrations for generating the base tables. I am not really a SQL expert, I have done mostly Ruby on Rails ORM stuff in the past.

Sorry if this question has been asked before, I tried doing a lot of searches, but my search terms were vague and hard to pinpoint ("how to add constraint that the second table property has a specific value, when doing an association", etc.).

What I would like to accomplish is, how to say that my relationship column should also validate that the associated record has a specific property. So I have this:

await db.schema
  .createTable('key')
  .addColumn('id', 'uuid', c => c.primaryKey())
  .addColumn('name', 'varchar(32)', c => c.notNull())
  .addColumn('category', 'varchar(32)', c => c.notNull())
  .execute()

await db.schema
  .createTable('event')
  .addColumn('id', 'uuid', c => c.primaryKey())
  .addColumn('action_type_id', 'uuid', c =>
    c.references('key.id').notNull(),
  )

I am pretty sure that would be like this in raw SQL:

create table key {
  id uuid primary key,
  name varchar(32),
  category varchar(32),
};

create table event (
  id uuid primary key,
  action_type_id references key(id)
);

What I would like to do is something like this (pseudocode):

create table key {
  id uuid primary key,
  name varchar(32),
  category varchar(32),
};

create table event (
  id uuid primary key,
  action_type_id references key(id) where key(category) == 'category1'
);

How can I do something like that in plain PostgreSQL syntax? I can probably figure out how to apply it to the kysely library after that.

The key table will have many category/name pairs (unique(category, name)), where category is in some small set of values, and name is as well.

There is no way to do this directly, although there are (at least) 3 ways you can do this indirectly

1. Add a trigger on the event table on insert/update which looks up the category of the action_type_id and validates it.

2. Modify your tables to create a multi-column FK relationship on key(id, category), and then add a check constraint in the event table that requires category to be "category1"

3. Add an intermediate table to your schema, like "category1s", which contains key(id, category) and a check constraint on the desired category, but also contains its own individual id primary key column, which would then be referenced by the events table.

There are likely some other ways to approach this, just keep in mind that the "best" solution will depend on a number of factors such as how many rows you expect in each table, how often rows in each of these tables will be modified, and maybe most of all which one provides you with a mental model you are comfortable with.

Oh, and it may be worth saying that this isn't a Postgres limitation, but more of a general relational modeling problem. If you want to research it further, the term you are looking for is "Conditional Foreign Key".