Обсуждение: error creating table that worked under 7.0.3

- Postgresql 7.1RC2
- installed from the RPM on Red Hat 7.0 on x86
- fresh databases built (initdb)
- error happens connecting with psql on UNIX domain sockets,
but there's no evidence it's exclusive to that interface

I am not sure whether this is a bug or a tightening-up of a previous
loose SQL definition.
Previously, I was able to define constraints  that refer to the same
table within the table itself,
like so:

CREATE SEQUENCE incidents_id_seq;
GRANT ALL ON incidents_id_seq TO its;
CREATE TABLE incidents (
       -- generated sequence ID for this incident
       id INTEGER DEFAULT nextval('incidents_id_seq'),

       -- if the resolution is to determine that this
       -- bug is a duplicate, this will contain the
       -- duplicate ID
       duplicate_iid INTEGER NULL,

         -- if an incident is declared as a duplicate of another
        -- incident and that incident is deleted, cascade to
        -- delete this one too
        CONSTRAINT duplicate_iid_exists
         FOREIGN KEY(duplicate_iid) REFERENCES incidents(id)
         ON DELETE CASCADE
);

In 7.1rc2, this same DDL results in this error:

ERROR:  UNIQUE constraint matching given keys for referenced table
"incidents" not found

(I've excerpted the table--the real one is much larger.)

Also, a minor nit. While it's fine that the default installs no longer
enable TCP/IP networking
when starting with the /etc/init.d/postgresql script, a note should be
made of this change; maybe
I missed it. I use JDBC and was surprised at first when it didn't
connect until I realized what had happened.

Thanks for a great product, folks. I use it every day. :-)

--kd

> I am not sure whether this is a bug or a tightening-up of a previous
> loose SQL definition.
> Previously, I was able to define constraints  that refer to the same
> table within the table itself,
> like so:
>
> CREATE SEQUENCE incidents_id_seq;
> GRANT ALL ON incidents_id_seq TO its;
> CREATE TABLE incidents (
>        -- generated sequence ID for this incident
>        id INTEGER DEFAULT nextval('incidents_id_seq'),
>
>        -- if the resolution is to determine that this
>        -- bug is a duplicate, this will contain the
>        -- duplicate ID
>        duplicate_iid INTEGER NULL,
>
>          -- if an incident is declared as a duplicate of another
>         -- incident and that incident is deleted, cascade to
>         -- delete this one too
>         CONSTRAINT duplicate_iid_exists
>          FOREIGN KEY(duplicate_iid) REFERENCES incidents(id)
>          ON DELETE CASCADE
> );
>
> In 7.1rc2, this same DDL results in this error:
>
> ERROR:  UNIQUE constraint matching given keys for referenced table
> "incidents" not found

You'll need something like:
id INTEGER DEFAULT nextval('incidents_id_seq') UNIQUE,
(or PRIMARY KEY) for the definition of id for the above to be
legal.

SQL technically only allows references to the cols belonging to a
UNIQUE or PRIMARY KEY constraint (and all the cols of
said constraint) but we didn't check that previously, mostly because
we don't prevent you from dropping the constraint implicitly (by
deleting the unique index) later out from under it, but the create
time check fixes some loopholes in any case.




_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

"Kyle F. Downey" <kdowney@amberarcher.com> writes:
> In 7.1rc2, this same DDL results in this error:

> ERROR:  UNIQUE constraint matching given keys for referenced table
> "incidents" not found

7.1 enforces the requirement that the referenced column be marked UNIQUE
(or PRIMARY KEY, which implies UNIQUE).  Seems to work if you add that.

            regards, tom lane