Postgres check constraint on `TEXT[]` using a normalized solution

Question

Similar to other questions asked but haven't found a normalized solution for TEXT[]:

Postgres check constraint in text array for the validity of the values

• does not use another table as a check

https://dba.stackexchange.com/questions/250659/constrain-array-values-to-an-allowed-set-of-values

• same solution as above but mentions a normalized solution

Check if value exists in postgres array for partitioning via check constraint

• similar

Postgres ENUM data type or CHECK CONSTRAINT?

• great answer and is a normalized solution but only for TEXT, not TEXT[]

---

I have two tables articles and valid_tags. valid_tags holds text values that are only allowed. When an article is INSERTed the tags TEXT[] column must be an array of valid tag values. I need to check those values against the valid_tags table.

CREATE TABLE articles (
  tags TEXT[]
);

CREATE TABLE valid_tags (
    name TEXT
);

I'm looking for a very similar solution as Postgres ENUM data type or CHECK CONSTRAINT? but with the constraint that column color_id is TEXT[].

Answer 1

The normalized and recommended solution is not to use an array, but a junction table between your tables. That would solve the problem implicitly.

Note that while you can write a check constraint that seems to do what you want, that check constraint is illegal and will break your database sooner or later. Don't do it.