Re: Weird message when creating PK constraint named like table

Hello, r.

You wrote:

rd> When I do this

rd> CREATE TABLE "T1"
rd> (
rd>   "T1_ID" bigint NOT NULL,
rd>   CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
rd> );

rd> I get the following message:

rd> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "T1" for table "T1"
rd> ERROR:  relation "T1" already exists
rd> ********** Error **********
rd> ERROR: relation "T1" already exists
rd> SQL state: 42P07

rd> It does NOT create either the table or the constraint, and the
rd> message is confusing because there is no relation by that name.

rd> The SQLSTATE 42P07 is described in the manual as only as "table
rd> undefined", and it is not clear if the intent is to allow or
rd> disallow the creation of a constraint called the same as the
rd> table in Postgresql. Oracle 11g allows this, but my feeling is that
rd> doing this should not be allowed, just as Postgresql handles it.

rd> I am complaining about the confusing error message which IMO is
rd> off-topic, not about how the DB handles this.

rd> Seen on Postgresql 9.1.2 Linux 64bit.


The quick answer is PRIMARY KEY constraint always has underlying system index
with the same name. Thus to implement CREATE statement above PostgreSQL should
create table with the name “T1″ and the index with the same name. This is impossible,
because tables and indexes are stored in the same system catalog pg_class (they share
the same namespace). That is where ambiguity appears. The same is true
for UNIQUE constraint.

On the other hand you may freely create CHECK constraint under such conditions:

CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" CHECK ("T1_ID" > 0 )
);

--
With best wishes,
 Pavel                          mailto:pavel@gf.microolap.com