Postgresql CONSTRAINT on column to global set of string/text values-CodePudding

I have a set of multiple tables in a single database which is roughly using a table inheritance pattern. The "class" is stored where required in a given table as a varchar column. Most tables have only one possible value but there are a few tables that can validly have any value. I also have a configuration table that basically lists all valid "class" names.

For the sake of maintainability and data consistency, I want to be able to impose a constraint on the various tables that can prevents operations from inserting records that reference a "class" that doesn't exist but I want the valid list to be "global" to the DB and I want it to be as performant as possible.

My intuition is to apply the constraint with something like class_name IN ('car', 'bike', 'fruit'). So far I've explored trying a CHECK CONSTRAINT with a subquery from the configuration table which isn't valid SQL and tried getting a stored FUNCTION to return the required set but that doesn't seem to be a kind of return type PostgreSQL functions support. I also tried assigning the set to a "global" config variable via ALTER DATABASE db SET abc.xyz = 1 but again, a set of values for IN doesn't appear to be valid SQL.

I know I could achieve this with a FOREIGN KEY but that means deletion of the configuration records impacts the values stored in the other tables and I'm concerned about performance.

I'm aware of the ENUM data type, but I want to store the "class" name as a varchar because text is just easier to work with.

Is there an approach that I might have missed that could be both "global" (i.e. maintained in one location in the DB only), stored as plain text and relatively performant?

CodePudding user response：

You can use a domain with a check constraint:

create domain class_name_domain as text
  constraint check_name check (value in ('car', 'bike'));
  
create table the_table
(
  id         integer primary key, 
  class_name class_name_domain
);