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
