Java-API does not recognize column with unique and foreign key constraint as part of foreign key

[erNail]

So, i have a column with an unique constraint and a foreign key constraint in Oracle. In the Java-API i use: column.isPartOfForeignKey() It returns false. Why is that ?

Small notice: I also created a GraphViz diagram with SchemaCrawler. There you can see the foreign key relation.

[schemacrawler]

Please provide more information, in the form of detailed SchemaCrawler logs. Please rerun SchemaCrawler with -loglevel=ALL, capture the debug logs, zip that up, and attach to this GitHub issue.

[erNail]

In my Java-Application i set log level to all with: applyApplicationLogLevel(Level.ALL).

The log which was printed in the console is attached. Is that the right one, or are there other log files created when using the Java-API ? I couldn't find any other information about the logs created when using the Java-API.

For creating the log file i only retrieved the table in which the foreign key is not recognized, and i also filtered the file so you don't need to go through all Excluding "SCHEMANAME.ATTACHMENT" since it does not match the include pattern.

Some more information: The table with the unique constraint and fk constraint is USER.BULKIMPORTERROR. The fk constraint name is FK_BIPERR_BIPCTL_01. It references from USER.BULKIMPORTERROR.BULKIMPORTNUMBER to USER.BULKIMPORTPROCESSCRONTROL.BULKIMPOERTNUMBER. The unique constraint name is UX_BIPERR_BULKIMPORTNUMBER. It's set on the column USER.BULKIMPORTERROR.BULKIMPORTNUMBER.

As you will see in the log file, no foreign key constraint is recognized.

Please tell me if you need any more information.

Here is the log: SchemaCrawlerBugLog.txt

[schemacrawler]

Eric, I need some important information from the beginning of the log, which contains details of your classpath and the SchemaCrawler options. Can you run from the command-line, and send me that information?

[schemacrawler]

Eric, since it looks like you are coding your own Java application, in addition, please also send me your code, so I can take a look. In your code, please CommandLineUtility.logSystemProperties();, and send me the log with that information.

[erNail]

So, i've run schemacrawler from the command-line. Not sure if this log is useful, since i'm running schemacrawler from a java-application and my local installation of schemacrawler is an older version. But here it is anyway: schemaCommandLine.txt

I also used CommandLineUtility.logSystemProperties(); and ran the java-application again. Here is the log: schemaJavaRun.txt

Furthermore, i've created a java class for you, which is better to look at. I was able to reproduce the error there: schemaJavaCode.txt

Also some additional info: This is the model of the table where i can't seem to get the foreign key (See column "BULKIMPORTNUMBER"), and the foreign key constraint in question.

[schemacrawler]

You have edited out the output of logSystemProperties(), so I cannot tell what is going on.

Here are some recommendations:

1. Ensure that schemacrawler-oracle-14.10.01.jar is on your classpath
2. Remove the following lines of code, and rerun

   options.setTableInclusionRule(new ExcludeAll());
   options.setTableInclusionRule(new RegularExpressionInclusionRule("CCP_USER.BULKIMPORTERROR"));

Let me know if that helps, and we can go forward from there.

[erNail]

Sorry, uploaded the wrong file. Here is the log with the logged system properties: schemaJavaRun.txt I also edited my previous comment.

Ensure that schemacrawler-oracle-14.10.01.jar is on your classpath

In Java i use a newer version of schemacrawler. I think the jar in in my classpath, since i use the maven repository and in the log you can see: C:\Users\exampleUser\.m2\repository\us\fatehi\schemacrawler\14.14.01\schemacrawler-14.14.01.jar

Remove the following lines of code, and rerun options.setTableInclusionRule(new ExcludeAll()); options.setTableInclusionRule(new RegularExpressionInclusionRule("USER.BULKIMPORTERROR"));

I only added these lines so the log would not be flooded with useless information. When removing the lines, the foreign key is still not recognized..

[schemacrawler]

Please include schemacrawler-oracle-14.14.01.jar on your classpath also, as I mentioned earlier.

[erNail]

Hello Sualeh, i've added the jar to the classpath. However, the foreign key is still not recognized. I've tried with schemacrawler-oracle-14.14.01.jar and schemacrawler-oracle-14.10.01.jar. I've also run the application with the lines removed, as you mentioned. No success.

[schemacrawler]

Eric, I am not able to help much with the edited logs that you have sent me. From the data that you have provided, it looks like there are no foreign keys defined in your database. Please run the following SQL statement to verify: FOREIGN_KEYS.sql.

[schemacrawler]

Also, please use the latest Oracle JDBC driver, since you are using Oracle JDBC driver 11.2.0.4.0 against database Oracle Database 12c Enterprise Edition Release 12.1.0.2.0, which could possibly cause issues too.

[erNail]

What data do you need exactly in the logs ? Apart from the system properties and the data related with the tables with the not-recognized foreign key ? I only removed the data not related to "BULKIMPORTERROR".

I've run your SQL statement, the foreign key FK_BIPERR_BIPCTL_01 is found:

Also, as i mentioned earlier, when i use schemacrawler to create a GraphViz-diagram, the foreign key is also recognized. To me it looks like the problem exists when using schemacrawler in java.

Totally forgot that the database was upgraded. Thanks for the notice. However, i get the same result with the latest ojdbc7.jar.

[erNail]

Sualeh, i was able to reproduce the issue in a new database with only the BULKIMPORTERROR and BULKIMPORTPROCESSCONTROL table. Could you try to reproduce it ?

Simply create a new database user and execute the following sql-statements: schemacrawler_error_testdb.txt

Than use schemacrawler in java and try to get the foreign key of BULKIMPORTERROR

[schemacrawler]

I will take a look.

[schemacrawler]

I was not able to reproduce the issue. Everything works perfectly. Please take a look at the code I used. The output from the code is:

SCHEMACRAWLER
o--> SCHEMACRAWLER.BULKIMPORTPROCESSCONTROL
     o--> SCHEMACRAWLER.BULKIMPORTPROCESSCONTROL.KEY (NUMBER)
     o--> SCHEMACRAWLER.BULKIMPORTPROCESSCONTROL.DB_VERSION (NUMBER)
     o--> SCHEMACRAWLER.BULKIMPORTPROCESSCONTROL.BULKIMPORTNUMBER (VARCHAR2)
o--> SCHEMACRAWLER.BULKIMPORTERROR
     o--> SCHEMACRAWLER.BULKIMPORTERROR.KEY (NUMBER)
     o--> SCHEMACRAWLER.BULKIMPORTERROR.DB_VERSION (NUMBER)
     o--> SCHEMACRAWLER.BULKIMPORTERROR.BULKIMPORTNUMBER (VARCHAR2) *** part of foreign key

and the diagram is

[erNail]

Interesting... I used your code and i get a different result:

SCHEMACRAWLER_ERROR
o--> SCHEMACRAWLER_ERROR.BULKIMPORTERROR
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTERROR.KEY (NUMBER)
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTERROR.DB_VERSION (NUMBER)
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTERROR.BULKIMPORTNUMBER (VARCHAR2)
o--> SCHEMACRAWLER_ERROR.BULKIMPORTPROCESSCONTROL
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTPROCESSCONTROL.KEY (NUMBER)
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTPROCESSCONTROL.DB_VERSION (NUMBER)
     o--> SCHEMACRAWLER_ERROR.BULKIMPORTPROCESSCONTROL.BULKIMPORTNUMBER (VARCHAR2)

So the problem is my machine. Any idea what could cause the problem ?

I'll run the code again on another machine. I'll tell you the results.

[schemacrawler]

Start from the basics: What operating system are you using (including version number), what version of Java, what database server version, and JDBC driver version? What version of SchemaCrawler are you using, and are you sure you have the latest version of the SchemaCrawler Oracle jar file? Are you using the same database user to create the schema as you are using to run SchemaCrawler? Are there any database permissioning issues in play?

[schemacrawler]

(Using a new machine is not going to guarantee that this will work. First, please make sure that you have consistent versions of everything, including database users and permissions.)