CODEWITHSUNDEEP
ruby-on-railspostgresql
Ethan
Ethan

Reputation: 60099

How can I remove unique constraints from a PostgreSQL DB?

I'm trying to write (Ruby) script that will drop all the foreign key and unique constraints in my PostgreSQL DB and then re-add them.

The FK part seems to be working OK.

However, dropping and recreating unique constraints isn't working.

I think the reason is that when the unique constraint is created, PostgreSQL creates an index along with it, and that index doesn't get automatically dropped when the unique constraint is dropped. So then when the script tries to re-add the unique constraint, I get an error like...

PG::Error: ERROR:  relation "unique_username" already exists
: ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username)

And indeed when I look at the DB in the pgAdmin GUI utility, that index exists.

The question is, how do I find it in my script and drop it?

Here's my script...

manage_constraints.rake

namespace :journal_app do

  desc 'Drop constraints'
  task :constraints_drop => :environment do

    sql = %Q|
SELECT
  constraint_name, table_catalog, table_name
FROM
  information_schema.table_constraints
WHERE
  table_catalog = 'journal_app_#{Rails.env}'
AND
  constraint_name NOT LIKE '%_pkey'
AND
  constraint_name NOT LIKE '%_not_null';
|

    results = execute_sql(sql)

    results.each do |row|
      puts "Dropping constraint #{row['constraint_name']} from table #{row['table_name']}."
      execute_sql("ALTER TABLE #{row['table_name']} DROP CONSTRAINT #{row['constraint_name']}")
    end

  end

  # --------------------------------------------------------------------------------------------------------------------

  desc 'Drops constraints, then adds them'
  task :constraints_add => :environment do

    Rake::Task['journal_app:constraints_drop'].invoke

    UNIQUE_KEYS = [
        {
            :name => 'unique_username',
            :table => 'users',
            :columns => ['username']
        },
        {
            :name => 'unique_email',
            :table => 'users',
            :columns => ['email']
        }
    ]

    FKs = [
        {
            :name => 'fk_entries_users',
            :parent_table => 'users',
            :child_table => 'entries',
            :on_delete => 'CASCADE'
        },
        {
            :name => 'fk_entries_entry_tags',
            :parent_table => 'entries',
            :child_table => 'entry_tags',
            :on_delete => 'CASCADE'
        },

        # etc...

    ]

    UNIQUE_KEYS.each do |constraint|
      sql = "ALTER TABLE #{constraint[:table]} ADD CONSTRAINT #{constraint[:name]} UNIQUE (#{constraint[:columns].join(', ')})"
      puts "Adding unique constraint #{constraint[:name]} to table #{constraint[:table]}."
      puts '  SQL:'
      puts "    #{sql}"
      execute_sql(sql)
    end

    FKs.each do |fk|
      sql = %Q|
ALTER TABLE #{fk[:child_table]} ADD CONSTRAINT #{fk[:name]} FOREIGN KEY (#{fk[:parent_table].singularize}_id)
  REFERENCES #{fk[:parent_table]} (id)
    ON UPDATE NO ACTION ON DELETE #{fk[:on_delete]}|.strip!
      puts "Adding foreign key #{fk[:name]}."
      puts '  SQL:'
      puts "    #{sql}"
      execute_sql(sql)
    end

  end

end

def execute_sql(sql)
  ActiveRecord::Base.connection.execute(sql)
end

Upvotes: 0

Views: 4242

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324345

First, why do such a thing? This has the feel of one of those "I've decided on solution Y to problem X, and am having a problem with solution Y that I'm asking about" - where the real answer is "use solution Z not solution Y to solve problem X". In other words, try explaining the underlying problem you are having, there might be a much better way to solve it.

If you must do it, query pg_catalog.pg_index inner join pg_class on pg_class.oid = pg_index.indexrelid for indexes that are not indisprimary and exclude anything with EXISTS (SELECT 1 FROM pg_constraint on pg_index.indrelid = pg_constraint.conindid).

eg:

SELECT pg_class.relname
FROM pg_index INNER JOIN pg_class ON (pg_class.oid = pg_index.indexrelid) 
INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE pg_index.indrelid = pg_constraint.conindid
) 
AND pg_index.indisprimary = 'f'
AND pg_namespace.nspname NOT LIKE 'pg_%';

Be aware that such queries may break in any major version transition as the pg_catalog is not guaranteed to retain the same schema across versions. Query the Pg version and use version-specific queries if necessary. Sound painful? It is, but it shouldn't generally be necessary, you're just doing something kind of weird.

For most purposes the very stable information_schema is quite sufficient.

Upvotes: 1

Related Questions