Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g03Lium76483 for ; Thu, 3 Jan 2002 16:44:58 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D2CDFD5E5; Thu, 3 Jan 2002 13:44:35 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id C87365BF1; Thu, 3 Jan 2002 13:44:35 -0800 (PST) Date: Thu, 3 Jan 2002 13:44:35 -0800 (PST) From: Stephan Szabo To: Tom Lane Cc: Katona Gabor , Subject: Re: How to union tables and have a field with UNIQUE constraint? In-Reply-To: <15666.1010091097@sss.pgh.pa.us> Message-ID: <20020103134126.Y14151-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/25 X-Sequence-Number: 6116 On Thu, 3 Jan 2002, Tom Lane wrote: > Stephan Szabo writes: > > I assume you want to reference id, so you'd need to do something like: > > > CREATE UNIQUE INDEX t_id_key ON t(id); > > Do we allow that (defining an index on a view)? [ tries it ] > Apparently so :-( > > I would argue that this should be disallowed, since it does not do > anything useful and can only mislead people into thinking that it > has some effect. Yeah, I was surprised that it let me do it as well. > > This is a hacky sort of way to do it (we allow you to make the internal > > form that a unique constraint has via create index even on views which > > I don't believe would ever be used :( ) and may not continue working > > forever. > > Since triggers on a view won't do anything either, I don't see how one > could expect REFERENCES to a view to work. The system ought to reject > all this stuff as unimplemented. I think that the insert/update on the fk table will work, but yeah, the parts on the pk table won't, so that's no good. I wouldn't want to bother for 7.2, but putting in something to make it so you can't reference a view is probably a good idea, at least until we can work out a way to make constraints on views work.