1

We are modeling the concept of a global "reference code" for some of the records in different tables in our system (e.g., ABC123). A "reference code" needs to be unique, easy to say over the phone, and can refer to a handful of different entities in our platform (e.g., people or places).

The criteria is:

  • one pool of codes that is unique across different tables of records
  • each record must have a code (not null)

How do I enforce uniqueness of this value across tables (either via a foreign key or just the value itself?)

CREATE TABLE reference_codes (
    code public.citext NOT NULL
);

CREATE UNIQUE INDEX index_reference_codes_on_code ON public.reference_codes USING btree (code);

CREATE TABLE people (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

CREATE TABLE places (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

-- now that I have two tables with foreign keys, 
-- how do I ensure they do not point to the same reference_code row?

In this case, both a person and a place could end up referencing the same code (which we do not want). Any suggestions? My internet search kung fu isn't turning up enough on its own.

I thought about perhaps a custom constraint that would check each table that is foreign keyed to the reference_codes table to ensure uniqueness, but it seems cumbersome (especially when more and more tables are added).

Or perhaps a materialized view which combines all the codes across tables? I am not sure what would be best.

2
  • 1
    There is a trigger approach, but, given your requirements, is it an option to just use a unique prefix for each table/type of thing that is being tracked? P for people, L for places, etc. Then you can enforce uniqueness by having a CHECK constraint for the reference code prefix on each table, plus a regular unique index on the reference_code columns themselves. Advantage over triggers is speed & simplicity, but a nonstarter if part of the requirement is that reference codes for records of one type look indistinguishable from those for other types. – AdamKG Oct 8 '20 at 20:28
  • @AdamKG — thanks for the quick reply; that was one idea we had considered and maybe we will go that route in the end ... but was trying to avoid it if possible (one reason being we already have a legacy system of codes that we have to migrate). – thornomad Oct 8 '20 at 21:24
2

Use bigint values generated by a single sequence. Numbers are quite easy to say over the telephone.

Forget about the requirement of enforcing database-wide uniqueness. Unless someone manually messes with the data, the sequence will guarantee the requirement. The performance cost of enforcing such a requirement with database means would greatly outweigh its usefulness.

An alternative would be to have a unique alphabetic prefix for each table that you store in a "table of tables". Then each table has its own sequence, and you generate the primary key in a BEFORE INSERT trigger by concatenating the table's prefix with the sequence value. That would be slighly more expensive, but would lead to more pronouncable names.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.