Re: unable to drop a constraint

Hello Tom,

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

> Enrico Sirola <enrico.sirola@gmail.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.