Chris Curvey-3 wrote >> -----Original Message----- >> From: Tom Lane [mailto: > tgl@.pa > ] >> Sent: Thursday, August 07, 2014 2:50 PM >> To: Chris Curvey >> Cc: > pgsql-general@ >> Subject: Re: dump/restore with a hidden dependency? >> >> Chris Curvey < > ccurvey@ > > writes: >> > I have a database with the following structure: >> > Create table bar... >> > Create function subset_of_bar ... (which does a select on a subset of >> > "bar") Create table foo... >> > Alter table foo add constraint mycheck check subset_of_bar(id); >> >> Basically, that's broken in any number of ways, not only the one you >> tripped >> across. CHECK constraint conditions should never, ever, depend on >> anything except the contents of the specific row being checked. >> When you try to fake a foreign-key-like constraint with a CHECK, Postgres >> will check it at inappropriate times (as per your pg_dump problem) and >> fail >> to check it at other times when it really needs to be checked (in this >> case, >> when you modify table bar). >> >> You need to restructure so that you can describe the table relationship >> as a >> regular foreign key. Anything else *will* bite you on the rear. >> >> regards, tom lane > > Thanks for the heads-up. Given that my requirement doesn't change > (entries in foo must not only reference a row in bar, but must reference > row in a subset of bar), what would be the recommended path forward? You > can't reference a view. Using table inheritance feels like the wrong > solution. > > Perhaps a pair of triggers? An insert-or-update trigger on foo, and a > delete-or-update trigger on bar? > > Any other ideas? In no particular order: Triggers A Compound FK that applies the check of the of the first field to the subset defined by the second. i.e., ... FOREIGN KEY (b_id, b_scope) REFERENCES bar (b_id, b_scope) David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/dump-restore-with-a-hidden-dependency-tp5814072p5814118.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
