Foreign key to column on same table at table creation time

[Thom Brown]

I've noticed (at least in pgAdmin III v1.8.4) that I can't create a foreign key constraint pointing to the table I'm in the process of creating.  I can do it manually though.  Here's an example:

CREATE TABLE testtable

(

  id serial NOT NULL,

  parent_id integer,

  CONSTRAINT pk_testtable_id PRIMARY KEY (id),

  CONSTRAINT fk_testtable_parent_id FOREIGN KEY (parent_id)

      REFERENCES testtable (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

I could achieve this if I create the table then apply the foreign key, but it would be nice to be able to do this one step.  Is it possible to add this for a future release?

Thanks

Thom

[Dave Page-7]

2009/10/4 Thom Brown <thombrown@...>:

> I've noticed (at least in pgAdmin III v1.8.4) that I can't create a foreign key constraint pointing to the table I'm in the process of creating.  I can do it manually though.  Here's an example:
> CREATE TABLE testtable
> (
>   id serial NOT NULL,
>   parent_id integer,
>   CONSTRAINT pk_testtable_id PRIMARY KEY (id),
>   CONSTRAINT fk_testtable_parent_id FOREIGN KEY (parent_id)
>       REFERENCES testtable (id) MATCH SIMPLE
>       ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> I could achieve this if I create the table then apply the foreign key, but it would be nice to be able to do this one step.  Is it possible to add this for a future release?

The code to implement it in the current dialogues would be quite messy
I expect. There is a TODO item which has had a little work put into it
already to replace the main table dialogue and the sub dialogues with
one 'uber' window - that project should make it easier to implement
something like this in the future.

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

--
Sent via pgadmin-support mailing list (pgadmin-support@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

[Thom Brown]

2009/10/6 Dave Page <dpage@...>

The code to implement it in the current dialogues would be quite messy
I expect. There is a TODO item which has had a little work put into it
already to replace the main table dialogue and the sub dialogues with
one 'uber' window - that project should make it easier to implement
something like this in the future.

It's obviously not major, but might give the impression such constraints can't be applied.  More of a useful nice-to-have.

Thanks Dave!

Thom