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. Caveat: I cannot split the “things” table into two. bye, //mirabilos -- tarent solutions GmbH Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/ Tel: +49 228 54881-393 • Fax: +49 228 54881-235 HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941 Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
