schemacrawler

SchemaCrawler Java API retrieve check column constraints

Per this question and its comments, in order to obtain constraints (I'm particularly interested in check constraints), one must do the following:

  1. Include a .jar corresponding to their DB driver of choice, like schemacrawler-postgresql or schemacrawler-sqlite.

  2. Set detail level to detailed (or higher), like so:

val optionsBuilder = SchemaCrawlerOptionsBuilder.builder()
    .withLoadOptions(LoadOptionsBuilder.builder()
        .withSchemaInfoLevel(SchemaInfoLevelBuilder.detailed())
        .toOptions()
)


val options = optionsBuilder.toOptions()
val catalog = SchemaCrawlerUtility.getCatalog(dataSource.get(), options)
  1. Use Table.getTableConstraints() method.

I have included .jar files for PostgreSQL, SQLite, MySQL, but I only get the constraints for PostgreSQL, while for both SQLite and MySQL the results are empty (and there are not only check constraints, but also not null constraints, but no sign of them either; this info can be retrieved by checking if the column is nullable directly, but no such API for check constraints). Are there any additional steps I haven't considered? Including the .jar for MySQL seemed to help the OP of the question linked above.

My dependencies are as follows:

implementation("us.fatehi:schemacrawler:16.9.3")
implementation("us.fatehi:schemacrawler-mysql:16.9.3")
implementation("us.fatehi:schemacrawler-sqlite:16.9.3")
implementation("us.fatehi:schemacrawler-postgresql:16.9.3")
Solution

  • I think you are doing everything correctly. SchemaCrawler sources its information from what the JDBC driver provides, and from the database's data dictionary (or INFORMATION_SCHEMA views). SchemaCrawler will not infer any metadata. So, in your case, if neither the MySQL JDBC driver nor the MySQL INFORMATION_SCHEMA report constraints, SchemaCrawler will not retrieve them. In this case, if you want to find NULL constraints, your best bet is to traverse the metadata and look for columns that are not nullable. You can build a utility method to do this, or submit an SchemaCrawler enhancement request for this utility. I do not have a good suggestion for how you can obtain check constraints if neither the JDBC driver nor the data dictionary views report them.

    Sualeh Fatehi, SchemaCrawler