On 03/31/2017 08:21 AM, Thorsten Glaser wrote:
On Fri, 31 Mar 2017, Adrian Klaver wrote:
① that using a CHECK constraint to check data from another table
is wrong (but not why), and
Because that is a documented limitation:
https://www.postgresql.org/docs/9.6/static/sql-createtable.html
"Currently, CHECK expressions cannot contain subqueries nor refer to variables
other than columns of the current row. The system column tableoid may be
referenced, but not any other system column."
Ah, okay. So, …
I also have a more generic suggestion to use an FK instead of a
CHECK constraint, although I’m not sure that this wouldn’t require
… this would be the proper fix, but…
changes to the application code, and I *am* sure that VIEWs have
penalties to the query optimiser (probably not a big issue here,
though).
I was thinking about…
CREATE VIEW vw_things_parents AS SELECT * FROM things WHERE
standalone=FALSE;
CREATE VIEW vw_things_children AS SELECT * FROM things WHERE
standalone=TRUE;
DROP TABLE derived_things;
CREATE TABLE derived_things (
parent BIGINT NOT NULL REFERENCES vw_things_parents(pk),
child BIGINT NOT NULL REFERENCES vw_things_children(pk),
arbitrary_data TEXT NOT NULL,
PRIMARY KEY (parent, child)
);
This, however, gives me:
ERROR: referenced relation "vw_things_parents" is not a table
… this.
Can you suggest a better way to do this? An application developer
coworker said to just drop the constraint and do the check in the
application, but I work under the assumption that the SQL part is
less code, less buggy, less often touched, and only by people who
have somewhat a measure of experience, so I declined.
Implement it as an ON INSERT/UPDATE trigger on derived_things?
Caveat: I cannot split the “things” table into two.
bye,
//mirabilos
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
