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);
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 |