Why you cannot create table and PK constraint with the same name

Very interesting message appeared on the pgsql-bugs@postgresql.org list today:

When I do this

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

I get the following message:

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

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

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

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

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 )
);

About these ads

Nerd.

Tagged with: , , , ,
Posted in PostgreSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: