Re: Enforcing referential integrity against a HSTORE column

On 01/01/2016 07:47 PM, Dane Foster wrote:
> 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

And the benefit is?

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

Yes, use a table:) I guess it comes down to the first question above and
what you are trying to achieve by moving to hstore. I use hstore and it
is very handy for storing ad-hoc data, however when I want all the the
RI whistle and bells I use table structures. The work has been done for
me by folks who know a lot more about this then I and it is one less
thing for me to code/worry about.

>
>
> Thank you for your consideration,​
>
> Dane
>


--
Adrian Klaver
adrian.klaver@aklaver.com