Check Constraint for Composite Value in PostgreSQL Column

Question

I have a scenario involving two tables in my project: "industries" and "sub_industries." In the "sub_industries" table, there's a column named "piped_name" which is a composite value of two columns: column1 + "|" + column2. Here, column1 refers to the "name" column in the "industries" table, and column2 refers to the "name" column in the "sub_industries" table.

For instance, if the "industries" table's "name" column contains "Agriculture" and the "sub_industries" table's "name" column contains "Farming", then the "piped_name" column in the "sub_industries" table should be "Agriculture|Farming".

I'm attempting to enforce the correctness of these composite values using a check constraint. I've tried using queryInterface.addConstraint() and raw SQL queries like await queryInterface.sequelize.query(). However, both approaches result in a "cannot use subquery in check constraint" error.

I have a few questions:

1)Is there an effective method to implement this check constraint for the "piped_name" column? 2)Could enforcing this constraint potentially impact the performance of operations on my tables? Should I consider alternatives? 3)Does this scenario violate any common database conventions or best practices? I'd appreciate any guidance or suggestions on how to address this issue effectively.

This is the code I was trying to use-->

 await queryInterface.sequelize.query(`
      ALTER TABLE "sub_industries"
      ADD CONSTRAINT "check_piped_name"
      CHECK (piped_name = (SELECT i.name || '|' || s.name
                           FROM "Industries" i
                           JOIN "sub_industries" s ON s."industry_id" = i."id"
                           WHERE s."industry_id" = i."id"));
    `, { transaction });