Oracle and Foreign Key Constraints

General Discussion
1

Hi,

How can I handle foreign key constraints for multiple databases?  For example, I have the following snippet from a changeset.

        <addForeignKeyConstraint baseColumnNames=“id”
                                baseTableName=“BaseTable”
                                constraintName=“FK”
                                onDelete=“NO ACTION”
                                onUpdate=“NO ACTION”
                                referencedColumnNames=“id”
                                referencedTableName=“AnotherTable”/>   

In Firebird it works fine, but not in Oracle.  I believe that “CASCADE” and “SET NULL” are the only choices that will work with Oracle. I thought about using parameters, where for firebird, mysql, etc the parameter would be set to “NO ACTION” and for oracle, it would just be an empty string.  But that violates the liquibase schema and results in a ChangeLogParseException.  It would be nice if the DB was Oracle that those two fields were just ignored if not set to “CASCADE” or “SET NULL”.  I believe that if auto incrementing is true in the column tag, it is ignored on oracle and firebird since it is not supported. 

Any other idea’s on how to get this to work for Oracle and other DB’s.

Thanks,
Alana

2

Handling database differences gets to be a difficult problem.  I generally prefer to have it throw an error rather than silently ignore them because you are actually asking for onDelete='NO ACTION" and so if your database cannot do what you ask, it should fail. 

One option is to have to separte changesets, one with the oracle version using dbms=“oracle” and one with dbms=“firebird”.  It is a bit uglier because you have to duplicate the code.  If you are using 2.0, you can override the validation to control what you want Oracle to do for onDelete=“NO ACTION”, though I am still working on documenting that.

The parameter option is the best, but like you said the XSD validation fails.  I have an open issue to look at how we can handle that better, so hopefully that will be an option in the future.

No great answer, sorry, but some options at least.

Nathan

3

Thanks. :)  I separated them into two different changesets, one for oracle and one for firebird.