Search Postgresql Archives

Re: Explicit Named Indexes for Constraints

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Jeff Larsen" <jlar310@xxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux