How to union tables and have a field with UNIQUE constraint?

From: Katona Gabor <katonag(at)dragon(dot)klte(dot)hu>
To: pgsql-sql(at)postgresql(dot)org
Subject: How to union tables and have a field with UNIQUE constraint?
Date: 2001-12-28 13:36:55
Message-ID: Pine.GSO.4.10.10112281351460.8286-100000@dragon
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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?

CREATE SEQUENCE t1_id_seq MINVALUE 1 MAXVALUE 49;
CREATE TABLE t1 (
id INTEGER PRIMARY KEY DEFAULT nextval('t1_id_seq'),
foo TEXT);
CREATE UNIQUE INDEX t1_id_key ON t1 (id);

CREATE SEQUENCE t2_id_seq MINVALUE 50;
CREATE TABLE t2 (
id INTEGER PRIMARY KEY DEFAULT nextval('t2_id_seq'),
foo TEXT,
bar TEXT);
CREATE UNIQUE INDEX t2_id_key ON t2 (id);

CREATE VIEW t AS SELECT id,foo FROM t1 UNION SELECT id,foo FROM t2;

CREATE TABLE uniont (
tid INTEGER REFERENCES t;
info TEXT);

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Chester Carlton Young 2001-12-28 17:49:49 query by tableoid, oid
Previous Message Tom Lane 2001-12-28 05:20:44 Re: [SQL] An easy question about creating a primary key