Re: Explicit Named Indexes for Constraints

"Jeff Larsen" <jlar310(at)gmail(dot)com> writes:
> In Informix, it is recommended to create explicit named indexes on
> columns for primary and foreign keys prior to creating the
> constraints. Otherwise, the server create the indexes for you with
> meaningless names. This is not generally a problem, except when you
> dump the schema, you get all the constraint indexes in the DDL,
> exported as if they were explicitly created, but with the server
> generated names. It's a mess to sort through.

Ugh. In PG, you can specify the names for server-generated indexes;
they're just the same names given to the constraints:

CREATE TABLE foo (f1 int constraint foo_primary_key primary key);

The index underlying this constraint will be named foo_primary_key.
If you leave off the "constraint name" clause then you get an
autogenerated name, but it's not so meaningless that there's a strong
need to override it --- in this example it'd be "foo_pkey".

Manual creation of indexes duplicating a constraint is definitely
*not* the thing to do in PG; you'll end up with redundant indexes.

> What's the recommended procedure in PG? At first glance it appears
> that PG hides the implicit indexes from you at all times, including
> pg_dump.

I wouldn't say they are "hidden", you just don't need to mention them
separately in the DDL commands.

regards, tom lane