Re: unable to drop a constraint

Hello Tom,

Il giorno 16/lug/08, alle ore 16:40, Tom Lane ha scritto:

> Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com> writes:
>> I have two tables, one has a field with a foreign key pointing to the
>> primary key of another table. When I to drop the first table, I get
>> the following error:
>
>> test=# drop table user;
>> ERROR: "customer_pkey" is an index
>
> I don't think you're being entirely truthful with us, because "user"
> is a reserved word. If you'd typed exactly that, you would have
> gotten
> regression=# drop table user;
> ERROR: syntax error at or near "user"
> LINE 1: drop table user;
> ^

You are right, the table name is slightly different, anyway I simply
searched the original table name and replaced it with "user". You will
now be able to reproduce it anyway, because I can't reproduce the
behaviour myself recreating the schema on another cluster.

> I'm not certain if this is just pilot error or if there really is
> something odd going on, but obfuscating what you're doing doesn't
> make it easier to decipher. Please show what you really typed and
> what you really got back. psql \d output for the two tables might
> be helpful too.

please consider the old psql session, just s/sps_user/user and s/
sps_customer/customer
Here's what you requested:

-bash-3.1$ psql sps_test
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

sps_test=# set role sps;
SET
sps_test=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+-------
public | schema_version | table | slony
sps | enabled_customer | view | sps
sps | group_to_customer | table | sps
sps | sps_customer | table | sps
sps | sps_user | table | sps
(5 rows)

sps_test=> \d sps_user
Table "sps.sps_user"
Column | Type |
Modifiers
-------------+-----------------------------
+----------------------------------------------------------
name | character varying(32) | not null
role | name | not null
password | password |
created | timestamp(3) with time zone | not null default
(now())::timestamp(3) with time zone
valid_until | timestamp(3) with time zone | not null default
'infinity'::timestamp(3) with time zone
email | character varying(100) |
Indexes:
"sps_user_pkey" PRIMARY KEY, btree (name)

sps_test=> \d sps_customer
Table "sps.sps_customer"
Column | Type |
Modifiers
--------------+-----------------------------
+----------------------------------------------------------
name | name | not null
description | text |
ftp_site | character varying |
ftp_user | character varying |
ftp_password | character varying |
created | timestamp(3) with time zone | not null default
(now())::timestamp(3) with time zone
valid_until | timestamp(3) with time zone | not null default
'infinity'::timestamp(3) with time zone
Indexes:
"sps_customer_pkey" PRIMARY KEY, btree (name)

Thanks for your help,
e.