X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D9C245E4249 for ; Fri, 20 Aug 2004 12:05:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50447-05 for ; Fri, 20 Aug 2004 15:05:37 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id B46335E40BB for ; Fri, 20 Aug 2004 12:05:28 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 808F7356DF; Fri, 20 Aug 2004 08:04:13 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7E756356DE; Fri, 20 Aug 2004 08:04:13 -0700 (PDT) Date: Fri, 20 Aug 2004 08:04:13 -0700 (PDT) From: Stephan Szabo To: Richard Huxton Cc: Josh Berkus , Jan Wieck , Markus Bertheau , olly@lfix.co.uk, pgsql-sql@postgresql.org Subject: Re: multi column foreign key for implicitly unique columns In-Reply-To: <4125AE16.8070401@archonet.com> Message-ID: <20040820073854.L89156@megazone.bigpanda.com> References: <1092734724.2627.4.camel@dicaprio.akademie1.de> <200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com> <200408181005.13230.josh@agliodbs.com> <4125AE16.8070401@archonet.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/225 X-Sequence-Number: 18722 On Fri, 20 Aug 2004, Richard Huxton wrote: > It'd be nice to say something like: > > ALTER TABLE status ADD CONSTRAINT user_status_fk > FOREIGN KEY (status) WHERE relation = 'users' > REFERENCES users(status); > > And the flip-side so you can have: > > ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk > FOREIGN KEY (trans_id) > REFERENCES transactions(trans_id) WHERE trans_type='CHQ'; > > Actually, since we can have a "unique index with where" this second form > should be do-able shouldn't it? Maybe, but there are some issues about how the feature would be defined. What is legal in those WHERE clauses? Can it refer to columns of the other table? Does the condition need to be immutable? If not, can it contain subselects? Can one use referentials actions on the constraint? If so, which rule is used for the second if a row is updated from having 'CHQ' to something else? Is it update because that's the original command, in which case things like update cascade will still error, or is it delete because the row is disappearing from the table created with the where clause? SQL has assertions which would presumably be able to handle the general constraints above which should have questions like this defined (and doesn't have referential actions I believe). It might be better to implement those if one was going to do it.