RE: pg_dump / Unique constraints

At 15:50 22/11/00 +0800, Christopher Kings-Lynne wrote:
> >I've been examining the pg_dump source and output, and I've come to the
> >conclusion that I can modify it so that UNIQUE constraints
> appear as part of
> >the CREATE TABLE statement, rather than as a separate CREATE INDEX.
> ...
> >Is there any problem with me working on this?
>
> I actually don't think it's a good idea to force things to work that way.

Why, exactly?

What's the difference between this:

--
create table test (
a int4,
constraint "name" unique (a)
)
--

and this:

--
create table test (
a int4
)
create unique index "name" on "test" using btree ( "a" "int4_ops" );
--

I note that when a table is dropped, any unique constraints (in fact all
indices) associated with it are also dropped...

> Perhaps as an *option*, but even then I'd be inclined to append them as a
> series of 'ALTER TABLE ADD CONSTRAINT...' statements.

As far as I can tell, Postgres 7.0.3 only supports adding fk constraints.
The CVS version seems to support adding CHECK constraints, but other than
that, it has to be added as an index. If you're a database user, it's
conceptually better to see right in your table that you've added a named (or
not) unique constraint, rather than noticing at the bottom of the file that
there's some unique index on one of your columns (IMHO).

Chris