[Solved] Liquibase, create foreign keys in Oracle, preconditions

EverSQL Database Performance Knowledge Base

Liquibase, create foreign keys in Oracle, preconditions

Database type:

I have a production and a QA instance of my application into which I'm integrating Liquibase. This means DDL and data already exists (or not if on development box). I have to create a changeLog which records everything as RAN on the non-empty DBs but execute actually on empty DBs. I'm on a good way but I'm a bit stuck with creating the foreign keys. (the database is Oracle).

(In general I'm creating preconditions which expects various objects to NOT exists and on fail MARK_RAN the change).

I find difficulties writing a correct precondition when I don't know the exact name of foreign keys, which may or may not exist. There is <foreignKeyConstraintExists> tag in liquibase (precondition) but it takes only schemaName and foreignKeyName attributes (and they are required). I don't know the foreign key names for sure in these instances as they are out of my control.

You can write custom SQL in preconditions like:

<changeSet id="1" author="bob">
    <preConditions onFail="WARN">
        <sqlCheck expectedResult="0">select count(*) from oldtable</sqlCheck>
    </preConditions>
    <dropTable tableName="oldtable"/>
</changeSet>

So I only have to create a custom SQL query which can check if a column on table A has foreign key referencing table B and use the result as a precondition. This is where my problem is because you can do it in Oracle but it's quite bloat:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       c.r_owner, c_pk.table_name r_table_name
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                        AND c.r_constraint_name = c_pk.constraint_name
  WHERE c.constraint_type = 'R' AND a.table_name = 'MY_TABLE'
  AND a.column_name = 'MY_COLUMN'
  AND c_pk.table_name = 'MY_OTHER_TABLE';

This prints a row if a foreign key exists on MY_COLUMN of MY_TABLE which references to MY_OTHER_TABLE. After rewriting it to COUNT you can check if there's foreign key without knowing it's name.

My question: I have dozens of foreign keys, do I really have to write this big SQL such dozens of times? Any suggestions, like outsourcing this to some function? Thanks!

Would it worth asking Liquibase developers to make <foreignKeyConstraintExists> 's name attribute optional and introduce the referenced table attribute alogn with local column name?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX all_columns_idx_table_name_column_name ON all_cons_columns (table_name,column_name);
CREATE INDEX all_constraints_idx_constra_table_owner_constra ON all_constraints (constraint_type,table_name,owner,constraint_name);
The optimized query:
SELECT
        a.table_name,
        a.column_name,
        a.constraint_name,
        c.owner,
        c.r_owner,
        c_pk.table_name r_table_name 
    FROM
        all_cons_columns a 
    JOIN
        all_constraints c 
            ON a.owner = c.owner 
            AND a.constraint_name = c.constraint_name 
    JOIN
        all_constraints c_pk 
            ON c.r_owner = c_pk.owner 
            AND c.r_constraint_name = c_pk.constraint_name 
    WHERE
        c.constraint_type = 'R' 
        AND a.table_name = 'MY_TABLE' 
        AND a.column_name = 'MY_COLUMN' 
        AND c_pk.table_name = 'MY_OTHER_TABLE'

Related Articles



* original question posted on StackOverflow here.