From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Jeff Larsen" <jlar310(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Explicit Named Indexes for Constraints |
Date: | 2007-10-21 20:13:11 |
Message-ID: | 28744.1192997591@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | paul rivers | 2007-10-21 20:40:24 | Re: looking for some real world performance numbers |
Previous Message | Dave Cramer | 2007-10-21 20:02:16 | Re: looking for some real world performance numbers |