Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g03I2Fm57287 for ; Thu, 3 Jan 2002 13:02:15 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id B275CD5E6; Thu, 3 Jan 2002 10:02:07 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A835F5BF1; Thu, 3 Jan 2002 10:02:07 -0800 (PST) Date: Thu, 3 Jan 2002 10:02:07 -0800 (PST) From: Stephan Szabo To: Katona Gabor Cc: Subject: Re: How to union tables and have a field with UNIQUE constraint? In-Reply-To: Message-ID: <20020103095733.C12608-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/22 X-Sequence-Number: 6113 On Fri, 28 Dec 2001, Katona Gabor wrote: > > I have the followinig SQL code (of course the tables contain more useful > data). When I load it into PostgreSQL via \i foo.txt it produces the > folloving error massage: > UNIQUE constraint matching given keys for referenced table "t" not found > The task I want to do with this code: Making a table t from t1 and t2 using > the id and the foo column. Table t shoud have one id and one foo column, > holding all the rows from t1 and t2, this is why I use UNION. As you can see, > the id column of t1 and t2 is PRIMARY KEY, therefore UNIQUE and the prepared > sequences provide that no rows can share the same id even after UNION. > > How can I correct the code to work? Is there any working way of doing such > things? I assume you want to reference id, so you'd need to do something like: CREATE UNIQUE INDEX t_id_key ON t(id); and in the uniont definition, something like: tid INTEGER REFERENCES t(id), 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.