Renaming Database Constraints with Liquibase

We used hibernate to set up our initial database schema. Hibernate generates constraint names like: fk_jwy3ip299dvht6bhrf7kxnmap and 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:

key_name,table,column,join_table,join_table_column

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.

Drop constraints

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[1], row[0])

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[1], row[2], row[3], row[4])
    print template % (name, row[1], row[2], row[3], row[4])