0

How do you ensure that a field's values reference an existing table's name in a schema? For example, in the following, how would I get the properties field in the item table to effectively make an ELEMENT REFERENCES on the table names in the property schema?

(Note: I believe ELEMENT REFERENCES is only going to be available in Postgres 9.6 or later, so I would create a item_property 'join' table.)

CREATE SCHEMA property;

CREATE TABLE item (
    id         SERIAL4 PRIMARY KEY,
    name       TEXT NOT NULL,
    -- e.g. iPhone, hamburger
    properties TEXT[] NOT NULL
    -- e.g. {'property_taste'}
);

CREATE TABLE property.taste (
    item_id        INT4 REFERENCES item PRIMARY KEY,
    description    TEXT NOT NULL
    -- e.g. sweet, sour, salty, savory
);

CREATE TABLE property.processor (
    item_id        INT4 REFERENCES item PRIMARY KEY,
    num_cores      INT2 NOT NULL,
    -- e.g. 4
    freq_mhz       INT2 NOT NULL
    -- e.g. 1600
);
Katie
  • 918
  • 1
  • 5
  • 18
  • You can't create a foreign key to catalog tables. But you don't really need `properties` in the first place, because your other tables are already referencing the `item` table - you can simply join them using the `item_id`. –  Feb 16 '16 at 17:49
  • @a_horse_with_no_name The reason I need `properties` is because some `item`s do not have a `processor`, others do not have a `taste`. (In my example above, a hamburger does not have a processor; an iPhone doesn't have a taste.) Is there a way I can represent this without building it into my queries? – Katie Feb 16 '16 at 17:55
  • If you don't really need the type safety for the attributes, I'd go for a single `hstore` column in the `item` table and get rid of the other two tables completely. If you want _some_ type safety. Put the attributes into a `jsonb` column instead of `hstore` –  Feb 16 '16 at 17:56

0 Answers0