How do I reverse a Postgresql unique index constraint in a rails migration?

0
votes

I created a unique index on my model like so:

add_index(:courses, :name, unique: true)

I no longer require the name to be unique. I think I have the same problem as this person, so I worked through those suggestions to no avail.

I receive errors like "ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_courses_on_name" when I try to add a record with the same name.

To remove the unique constraint I've tried:

  1. remove_index :courses, :name

    (migration succeeds, but later I get the same error suggesting there is still a uniqueness constraint)

  2. execute "ALTER TABLE courses DROP CONSTRAINT unique_index_courses_on_name"

  3. execute "ALTER TABLE courses DROP CONSTRAINT index_courses_on_name"

  4. execute "ALTER TABLE courses DROP CONSTRAINT unique_name"

  5. execute "ALTER TABLE courses DROP CONSTRAINT name"

  6. execute "ALTER TABLE courses DROP INDEX index_courses_on_name"

  7. execute "ALTER TABLE courses DROP INDEX courses_on_name"

  8. execute "DROP INDEX unique_courses_name"

(PG::UndefinedObject: ERROR: index "unique_courses_name" does not exist)

  1. execute "ALTER TABLE courses disable CONSTRAINT unique_courses_name"

(PG::SyntaxError: ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE courses disable CONSTRAINT unique_courses_name)

  1. execute "ALTER TABLE courses DROP INDEX unique_courses_name"

I'm on psql 9.4.4, Rails 4.2, and my last ounce of sanity. Thanks everyone.

1
ruby-on-railspostgresqlrails-migrations
Show the table definition from psql, e.g. \dt courses – Craig Ringer
@craig - it says no relations found in dev and can't find the db connection in production (I presume because it's a URL to the db rather than in /var on Heroku). I'm going to investigate why I'm getting this error despite it seeming to work fine. – Grant Nelson

1 Answers

0
votes

I think unique will be removed if you remove the old index and add it again

class RemoveUniqueFromCoursesName < ActiveRecord::Migration
  def change
    remove_index(:courses, :name)
    add_index(:courses, :name)
  end
end