We used hibernate to set up our initial database schema. Hibernate generates constraint names like:
fk_bc7w4arbgsiasoufirt1ec0na. While these are unique and of a length that works on all databases, they are not very descriptive.
We are using H2 and Postgres for our database (test, production) both of which support longer constraint names. Since we use Liquibase to deploy our database changes, this gave me a great way to rename all these constraints.
First I went into our Postgres instance and got a list of all the foreign key constraints.
SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY';
I exported this query to CSV. This gave me a file in this format:
We have over 50 constraints on our tables. There was no way I was planning to type out all of these changes in YAML. I ended up creating two python scripts to output the needed YAML.
import csv template = """ - dropForeignKeyConstraint: baseTableName: %s constraintName: %s""" datafile = open('./constraints_file.csv', 'r') datareader = csv.reader(datafile) data =  for row in datareader: data.append(row) for row in data: print template % (row, row)
Add back Constraints
import csv template = """ - addForeignKeyConstraint: constraintName: %s baseTableName: %s baseColumnNames: %s referencedTableName: %s referencedColumnNames: %s""" datafile = open('./constraints_file.csv', 'r') datareader = csv.reader(datafile) data =  for row in datareader: data.append(row) for row in data: name = "fk_%s_%s_%s_%s" % (row, row, row, row) print template % (name, row, row, row, row)