Обсуждение: Can't drop constraint?

Hello,

I don't know how they did this, but I have a table that has a compound
field constraint.  When I try to insert data into this table from an
app I'd developing the query fails because the data supposedly
violates the unique constraint on these fields.  The fields are first,
middle, and last names.  The name I'm trying to insert doesn't exist
in the database, yet I can't put it in.  When I try to drop the
constraint, which I can plainly see when I describe the table,
postgres tells me that the constraint doesn't exist.  How can I fix
this?

Thanks,
Carol

Carol Walter <walterc@indiana.edu> writes:
> I don't know how they did this, but I have a table that has a compound
> field constraint.  When I try to insert data into this table from an
> app I'd developing the query fails because the data supposedly
> violates the unique constraint on these fields.  The fields are first,
> middle, and last names.  The name I'm trying to insert doesn't exist
> in the database, yet I can't put it in.  When I try to drop the
> constraint, which I can plainly see when I describe the table,
> postgres tells me that the constraint doesn't exist.  How can I fix
> this?

Since you haven't shown us exactly what you're seeing, we're just
guessing ... but I'm wondering if the constraint has a mixed-case
name and you've forgotten to double-quote it.

            regards, tom lane

Carol Walter <walterc@indiana.edu> writes:
> I'm sorry, I should have included this.  Here is what I'm seeing.
> This is PostgreSQL 8.3.6 on Solaris 10.

> km_tezt=# \d "tblPeople";
>                                        Table "public.tblPeople"
>    Column  |          Type          |
> Modifiers
> ----------+------------------------
> +----------------------------------------------------------------
>   peopleId | integer                | not null default
> nextval('"tblPeople_peopleId_seq"'::regclass)
>   fName    | character varying(70)  |
>   mName    | character varying(70)  |
>   lName    | character varying(100) |
>   ivlweb   | boolean                |
>   cnsweb   | boolean                |
> Indexes:
>      "primary_key_tblPeople" PRIMARY KEY, btree ("peopleId")
>      "people_all_fields" UNIQUE, btree ("lName", "fName", "mName")

> km_tezt=# alter table "tblPeople" drop constraint "people_all_fields";
> ERROR:  constraint "people_all_fields" does not exist

Apparently this is just an index, not a constraint (the difference being
that it was made with CREATE INDEX, not ALTER TABLE ADD CONSTRAINT).
Try

DROP INDEX people_all_fields;

            regards, tom lane

On Dienstag 05 Mai 2009 Tom Lane wrote:
> Apparently this is just an index, not a constraint (the difference
> being that it was made with CREATE INDEX, not ALTER TABLE ADD
> CONSTRAINT). Try

Is there a performance difference? Or is it just a matter of taste which
one you use? Technically, are they both indices, or is the constraint a
check that's executed on insert/update, but doesn't store the values?

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

This did fix my problem.  I thought that it was a constraint because
the error message I got said that the action was violating the unique
constraint.  I didn't realize until later that actually meant the
unique constraint on the index,

I'd be interested in the question here, as well.

Carol

On May 6, 2009, at 1:45 AM, Michael Monnerie wrote:

> On Dienstag 05 Mai 2009 Tom Lane wrote:
>> Apparently this is just an index, not a constraint (the difference
>> being that it was made with CREATE INDEX, not ALTER TABLE ADD
>> CONSTRAINT). Try
>
> Is there a performance difference? Or is it just a matter of taste
> which
> one you use? Technically, are they both indices, or is the
> constraint a
> check that's executed on insert/update, but doesn't store the values?
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc    -----      http://it-management.at
> // Tel: 0660 / 415 65 31                      .network.your.ideas.
> // PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
> // Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Michael Monnerie <michael.monnerie@is.it-management.at> writes:
> On Dienstag 05 Mai 2009 Tom Lane wrote:
>> Apparently this is just an index, not a constraint (the difference
>> being that it was made with CREATE INDEX, not ALTER TABLE ADD
>> CONSTRAINT). Try

> Is there a performance difference?

No.  A primary key or unique constraint is implemented by creating
a unique index (and, for PK, also by creating NOT NULL constraints
on the columns).  After that, the only visible difference is that
there's an entry in pg_constraint, or not.  There are some minor
behavioral differences --- if memory serves, you need a PK constraint
entry to persuade a REFERENCES constraint that it should consider
a given column as the default reference target --- but no performance
difference.

            regards, tom lane

On Mittwoch 06 Mai 2009 Tom Lane wrote:
> No.  A primary key or unique constraint is implemented by creating
> a unique index (and, for PK, also by creating NOT NULL constraints
> on the columns).  After that, the only visible difference is that
> there's an entry in pg_constraint, or not.  There are some minor
> behavioral differences --- if memory serves, you need a PK constraint
> entry to persuade a REFERENCES constraint that it should consider
> a given column as the default reference target --- but no performance
> difference.

To take it to a point: Creating an index has advantages over
constraints, without any disadvantage? So I will always only use
indices, not constraints, to have a more uniform db model.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4