Re: Enforcing referential integrity against a HSTORE column - Mailing list pgsql-general

On 01/02/2016 08:13 AM, Dane Foster wrote:

Ccing list.
> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     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?
>     ​
>
> ​
> The benefit is supposed to be client side simplicity. The data in these
> particular tables are ultimately consumed by JavaScript as JSON on the
> front end to populate/maintain a dynamic HTML for​m. So I was attempting
> to build a model that more closely reflects how the data is used because
> the people using the data aren't SQL folks and the code that converts
> the data from table/rows to JSON is not straight forward for my audience.

In that case you may want to look at the JSON types, json and/or
jsonb(depending on Postgres version):


http://www.postgresql.org/docs/9.4/interactive/datatype-json.html

>
>
>         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.
>
>
> ​I agree.​ I should keep the table.
>
> ​While thinking deeply about your question it dawned on me that I can
> have it both ways. So my new solution is to create a view that looks
> like the foo table. This way I get built-in referential integrity via
> foreign keys and a programmer friendlier view.
>
> Thanks for shaking up my perspective,
>
> Dane​
>


--
Adrian Klaver
adrian.klaver@aklaver.com