Composite key with user-supplied string column, foreign keys

Question

Let's say I have the following table

TABLE subgroups (
    group_id        t_group_id NOT NULL REFERENCES groups(group_id),
    subgroup_name   t_subgroup_name NOT NULL,
    more attributes ...
)

• subgroup_name is UNIQUE to a group(group_id).
• A group can have many subgroups.
• The subgroup_names are user-supplied. (I would like to avoid using a subgroup_id column. subgroup_name has meaning in the model and is more than just a label, I am providing a list of predetermined names but allow a user to add his owns for flexibility).
• This table has 2 levels of referencing child tables containing subgroup attributes (with many-to-one relations);

I would like to have a PRIMARY KEY on (group_id, upper(trim(subgroup_name)));

From what I know, postgres doesn't allow to use PRIMARY KEY/UNIQUE on a function. IIRC, the relational model also requires columns to be used as stored.

CREATE UNIQUE INDEX ON subgroups (group_id, upper(trim(subgroup_name))); doesn't solve my problem as other tables in my model will have FOREIGN KEYs pointing to those two columns.

I see two options.

Option A)

• Store a cleaned up subgroup name in subgroup_name
• Add an extra column called subgroup_name_raw that would contained the uncleaned string

Option B)

• Create both a UNIQUE INDEX and PRIMARY KEY on my key pair. (seems like a huge waste)

Any insights?

Note: I'm using Postgres 9.2

Answer 1

Actually you can do a UNIQUE constraint on the output of a function. You can't do it in the table definition though. What you need to do is create a unique index after. So something like:

CREATE UNIQUE INDEX subgroups_ukey2 ON subgroups(group_id, upper(trim(subgroup_name)));

PostgreSQL has a number of absolutely amazing indexing capabilities, and the ability to create unique (and partial unique) indexes on function output is quite underrated.