Re: Enforcing referential integrity against a HSTORE column

On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> 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)));
>

​​I also agree w/ Adrian's comments and to that end I will be keeping the
original tables and building a view that does what I need on top of them.
But I really like your SQL solution. It is quite elegant and I suspect it
would run faster than mine for a large enough data set. So I'll be filing
it away in memory for use at some future point in time.
​

>
> 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
>
​
Thanks,

Dane
​