7

In PG:

I made a user table that includes unique emails, but later decided that emails should not be unique. I pushed changes to make my email field non-unique (I use an ORM, so I don't actually have the exact SQL that took place), but PG still won't let me use duplicate email addresses.

I checked the index and it's not unique, but there's a constraint keeping me from having duplicate email addresses. However I'm having trouble dropping this constraint. What am I doing wrong?

SQL> ALTER TABLE "users" DROP CONSTRAINT "unique_users_email"
PGError: ERROR:  constraint "unique_users_email" of relation "users" does not exist

SQL> UPDATE users SET email = 'test@test.com'
PGError: ERROR:  duplicate key value violates unique constraint "unique_users_email"
DETAIL:  Key (email)=(test@test.com) already exists.
AlexQueue
  • 6,353
  • 5
  • 35
  • 44
  • perhaps there is a place to commit changes? Also, try: `ALTER TABLE "users" disable CONSTRAINT "unique_users_email";` – jt234 Oct 16 '12 at 19:58
  • @jt234 That didn't work.`PGError: ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE "users" disable CONSTRAINT "unique_users_email";` My changes seem to be committing just fine, except for this problem. – AlexQueue Oct 16 '12 at 20:12
  • how about DISABLE TRIGGER ALL? – jt234 Oct 16 '12 at 20:16
  • Here is pretty much everything you can do with a table: [link](http://www.postgresql.org/docs/9.2/static/sql-altertable.html) – jt234 Oct 16 '12 at 20:27
  • @jt234 You can't `DISABLE` a `CONSTRAINT` in Pg. – Craig Ringer Oct 16 '12 at 23:33
  • 1
    @AlexQueue Please show the output of "\d+ users" in `psql`; it's hard to help you when working blind. It certainly sounds like you're in an odd situation. Exactly *what* ORM are you using? And what PostgreSQL version? – Craig Ringer Oct 16 '12 at 23:34
  • Sounds like you've still got a unique index. \d on the table, as Craig suggests, should show if you do. – Scott Marlowe Oct 17 '12 at 05:34
  • I ended up resetting the DB (I was still in test so it wasn't an issue). As such I can no longer reproduce this. – AlexQueue Jan 08 '13 at 20:57

1 Answers1

4

I bet that "unique_users_email" is actually the name of a unique index rather than a constraint. Try:

DROP INDEX "unique_users_email";

Recent versions of psql should tell you the difference between a unique index and a unique constraint when looking at the \d description of a table.

Josh Kupershmidt
  • 2,540
  • 21
  • 30
  • 1
    Be sure to specify the schema if the table is not part of the public schema: DROP INDEX myschema.unique_users_email; – buddamus Feb 02 '17 at 18:49