[GENERAL] Enforcing referential integrity against a HSTORE column

[Dane Foster]

Hello,

I'm moving a MySQL database to PostgreSQL and redesigning parts of it to
take advantage of PostgreSQL's richer type system and other advance
features. Currently I am attempting to replace a table of name/value pair
data w/ a hstore column. But now that the data will no longer be flattened
out in a table I need to manually handle referential integrity

So given:

CREATE TABLE xtra_fields(
  xfk SERIAL PRIMARY KEY,
  xtk INTEGER NOT NULL REFERENCES xtra_types,
  ...
);

CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
WITH keyz AS (SELECT skeys($1)::INT AS xfk)
SELECT
  (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
  =
  (SELECT COUNT(*) FROM keyz)
$$LANGUAGE SQL STABLE STRICT LEAKPROOF;

CREATE TABLE foo(
  id INTEGER NOT NULL CHECK (id > 0),
  ...
-- Extra fields where the keys are the xtra_fields.xfk values and the
values are the
-- data values for the specific xfk.
  xtra hstore CHECK (foo_xtra_fk(xtra))
);

is ​there a more efficient way of maintaining logical referential integrity?


Thank you for your consideration,​

Dane