> 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