SQLBuilder ForeignKeyConstraint - set cascade

Question

Using the API SQL Builder com.healthmarketscience.sqlbuilder.

DbForeignKeyConstraint constraint = dbTable.foreignKey("fk_" + tableName + "_" + foreignTableName,
        columnList.toArray(new String[0]),
        foreignTableName,
        foreignColumnList.toArray(new String[0]));

how to set the constraint so that it does ON UPDATE CASCADE and ON DELETE CASCADE?

Answer 1

To the best of my understanding, the library you're using (com.healthmarketscience.sqlbuilder) doesn't allow to declare a foreign key constraint with ON UPDATE/DELETE CASCADE. I've checked the code, and this functionality isn't implemented.

The reason for this could be that the authors of the library have not implemented this functionality yet, or that they don't intend to implement it for some reason. You should be able to reproduce the functionality with a TRIGGER, but again the llibrary doesn't seem to support those.

If this functionality is crucial to your application, you should probably switch to a library that allows for it.

Answer 2

For future readers, this functionality has been added to SQLBuilder in version 3.0.1. Once you generate the ForeignKeyConstraintClause instance, you can call setOnDeleteAction(ReferentialAction.CASCADE) (and same for update action).