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

Поиск
Список
Период
Сортировка
От Katona Gabor
Тема How to union tables and have a field with UNIQUE constraint?
Дата
Msg-id Pine.GSO.4.10.10112281351460.8286-100000@dragon
обсуждение исходный текст
Ответы Re: How to union tables and have a field with UNIQUE constraint?  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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
tablet 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);




В списке pgsql-sql по дате отправления:

Предыдущее
От: "Tom Kreiner"
Дата:
Сообщение: Getting Hour From a Time in Different Time Zone
Следующее
От: rickd@open-works.com (Rick Dayao)
Дата:
Сообщение: Conditional test