On Fri, Mar 31, 2017 at 8:21 AM, Thorsten Glaser <t.glaser@tarent.de> 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?
The usual solution is to add a validation trigger on the derived_things table. It is functionally the same as your CHECK constraint. You could couple it with a FK "child REFERENCES things (thing_id)".
If going down the trigger route you could consider adding an insert trigger on things which will also insert a record into a "child_things" table containing on the PK value in a one-to-one relationship. An FK on that table could be define ON UPDATE/ON DELETE CASCADE. The derived_things table could then point to child_things table for it FK. In effect you've just created two (materialized) views of the existing things view just like you tried above but this one works because its indexes and physical and so can be the target of a FK. Because of those properties is just requires a bit of trigger-driven maintenance.
Caveat: I cannot split the “things” table into two.
The original table stays so this shouldn't apply - you do need to be able to create new tables and triggers.
