Re: Enforcing referential integrity against a HSTORE column

On Sat, 2 Jan 2016 07:30:38 -0800
Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> > 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?

I second Adrian's comment on making sure that the benefit of HSTORE is
outweighing the drawback of having to write your own checks ... however,
if you decide that HSTORE is the right way to go, you may want to try
something more along the lines of this for your check:

SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1)));

Not tested, so it's possible that I have some typo or something; but overall
I've found that the NOT EXISTS construct can be very efficient in cases
like these.

--
Bill Moran