Enforcing referential integrity against a HSTORE column

From: Dane Foster <studdugie(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Enforcing referential integrity against a HSTORE column
Date: 2016-01-02 03:47:39
Message-ID: CA+WxinJi+3t+tGmdZjB8Y9JmipMp_9nmuv0uS=1a5KHbJ7_8OA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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?

Thank you for your consideration,​

Dane

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-01-02 15:30:38 Re: Enforcing referential integrity against a HSTORE column
Previous Message Andrew Bailey 2016-01-02 03:39:16 Efficiently selecting single row from a select with window functions row_number, lag and lead