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.