0

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

akp
  • 21
  • 1
  • 1
  • 6
  • Why would you want to store an un-cleaned-up version of subgroup_name? I'm trying to figure out your thinking. Surely you have clean versions of group+subgroup ids and a lookup table to give a display name. – Richard Huxton Jul 17 '13 at 15:09
  • I would want the un-cleaned-up version to display in the user interface. I would not want to display an uppercased-version and I want to make sure the name is case-insensitively unique. A subgroup will have members, messages and other objects attached to it. Tables containing these objects will have a foreign key to the subgroups table. I see a clean subgroup_name as an equivalent to a subgroup_id. – akp Jul 17 '13 at 15:19
  • 1
    So if it's for user display, you'll want to put it in a lookup table anyway. They'll presumably want to change the display-name at some point. They always do. – Richard Huxton Jul 17 '13 at 16:08

1 Answers1

0

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182