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

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

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.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I assume you want to reference id, so you'd need to do something like:

> CREATE UNIQUE INDEX t_id_key ON t(id);

Do we allow that (defining an index on a view)?  [ tries it ]
Apparently so :-(

I would argue that this should be disallowed, since it does not do
anything useful and can only mislead people into thinking that it
has some effect.

> 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.

Since triggers on a view won't do anything either, I don't see how one
could expect REFERENCES to a view to work.  The system ought to reject
all this stuff as unimplemented.
        regards, tom lane

On Thu, 3 Jan 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > I assume you want to reference id, so you'd need to do something like:
>
> > CREATE UNIQUE INDEX t_id_key ON t(id);
>
> Do we allow that (defining an index on a view)?  [ tries it ]
> Apparently so :-(
>
> I would argue that this should be disallowed, since it does not do
> anything useful and can only mislead people into thinking that it
> has some effect.

Yeah, I was surprised that it let me do it as well.

> > 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.
>
> Since triggers on a view won't do anything either, I don't see how one
> could expect REFERENCES to a view to work.  The system ought to reject
> all this stuff as unimplemented.

I think that the insert/update on the fk table will work, but yeah, the
parts on the pk table won't, so that's no good.  I wouldn't want to
bother for 7.2, but putting in something to make it so you can't reference
a view is probably a good idea, at least until we can work out a way
to make constraints on views work.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> Since triggers on a view won't do anything either, I don't see how one
>> could expect REFERENCES to a view to work.  The system ought to reject
>> all this stuff as unimplemented.

> I think that the insert/update on the fk table will work, but yeah, the
> parts on the pk table won't, so that's no good.  I wouldn't want to
> bother for 7.2, but putting in something to make it so you can't reference
> a view is probably a good idea, at least until we can work out a way
> to make constraints on views work.

Investigation showed that there were a couple of other utility
statements that were also missing adequate checks on permissions and
table type.  I've applied patches.
        regards, tom lane

> I would argue that this should be disallowed, since it does not do
> anything useful and can only mislead people into thinking that it
> has some effect.
> 
> Since triggers on a view won't do anything either, I don't see how one
> could expect REFERENCES to a view to work.  The system ought to reject
> all this stuff as unimplemented.
 OK, I understand the problem with referencing a view and I changed my code
to use CREATE TABLE AS instead of a view (the created table will change very
rarely, so I'll update it then via a trigger). Is this way ok now and will
work until the end of time :) ? And thanks for the answers.
    Gabor Katona

On Fri, 4 Jan 2002, Katona Gabor wrote:

>
>
> > I would argue that this should be disallowed, since it does not do
> > anything useful and can only mislead people into thinking that it
> > has some effect.
> >
> > Since triggers on a view won't do anything either, I don't see how one
> > could expect REFERENCES to a view to work.  The system ought to reject
> > all this stuff as unimplemented.
>
>   OK, I understand the problem with referencing a view and I changed my code
> to use CREATE TABLE AS instead of a view (the created table will change very
> rarely, so I'll update it then via a trigger). Is this way ok now and will
> work until the end of time :) ?

That doesn't sound like something that'll stop working, so you should be
safe.