Re: Enforcing referential integrity against a HSTORE column

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

Ccing list.
> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com