SchemaSpy automatically detects only Foreign Keys that reference a Primary Key

[riccardopedron96]

Hi,
Using SchemaSpy 6.2.4 I've noticed that only Foreign Keys that reference a Primary Key were automatically detected.
It's the intended behaviour?
If so, it's possible to add a command line flag to change that behaviour so that all FKs are automatically detected and made visibile in the relationship graphs?
Or maybe there is already a workaround for this?

Let me know if you need more details
Thanks
Riccardo

[npetzall]

Sorry for the delayed response.

We do check constraints information in the database and in that case it needs to be defined.

We have the implied, which I need to double check if it has that type of limitation. I can image it does since the scope might get to broad. There is an open issue that it behaves strangely with multiple children/parents.

I think we sadly have an old PR for adding support for composite keys.

Lastly we have the rails convention thing, there has been a feature request for supporting this thru patterns but we haven't gotten to it.

If you could provide a simple sample I could further advice you and also check.

If you only want it added to the documentation it could be added thru SchemaMeta.

[riccardopedron96]

Hi,
Thanks for the reply.
As for the sample, I have a schema with the following structure:

CREATE TABLE communication (
	id					NUMBER (11) NOT NULL,
	communication_code	VARCHAR2 (3) NOT NULL,
	description			VARCHAR2 (100) NOT NULL
);
ALTER TABLE communication ADD CONSTRAINT communication_pk PRIMARY KEY (id);

CREATE TABLE channel (
	id						NUMBER (11) NOT NULL,
	channel_code			VARCHAR2 (2) NOT NULL,
	description				VARCHAR2 (20),
	communication_code_fk	VARCHAR2 (3) NOT NULL
);
ALTER TABLE channel ADD CONSTRAINT channel_pk PRIMARY KEY (id);
ALTER TABLE channel ADD CONSTRAINT channel_fk1 FOREIGN KEY (communication_code_fk) REFERENCES communication (communication_code);

The FOREIGN KEY named channel_fk1 is not automatically detected by SchemaSpy and it appears in the generated documentation only if I add it in schemaspy.xml, as following:

<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="schemaspy.xsd">
    <tables>
        <table name="channel">
            <column name="communication_code_fk">
                <foreignKey table="communication" column="communication_code"/>
            </column>
        </table>
    </tables>
</schemaMeta>

Thanks
Riccardo

[sualeh]

@riccardopedron96 What database is this for?

[riccardopedron96]

Oracle

[sualeh]

@riccardopedron96 I am getting "ORA-02270: no matching unique or primary key for this column-list" for your sample code, since there is no unique constraint on communication.communication_code.

[sualeh]

@riccardopedron96 I suggest the following example to highlight your issue:

CREATE TABLE communication
(
  id NUMBER(11) NOT NULL,
  communication_code VARCHAR2(3) NOT NULL,
  description VARCHAR2(100) NOT NULL
);

ALTER TABLE communication
ADD CONSTRAINT communication_uq
  UNIQUE (communication_code);

CREATE TABLE channel
(
  id NUMBER(11) NOT NULL,
  channel_code VARCHAR2(2) NOT NULL,
  description VARCHAR2(20),
  communication_code_fk VARCHAR2(3) NOT NULL
);

ALTER TABLE channel
ADD CONSTRAINT channel_fk1
  FOREIGN KEY (communication_code_fk)
  REFERENCES communication (communication_code);

[npetzall]

@sualeh thanks for fixing the example.

The problem is that the FK dosen't reference a PK which seems to be a requirement for JDBC API DatabaseMetaData#getImportedKeys
The same goes for the getExportedKeys.

I've had a look at the code and also found the getCrossReference which SchemaSpy isn't using. But it's incorrectly implemented in the Oracle JDBC driver. It works exactly the same way as getImportedKeys or getExportedKeys requiring that one end is PK and other is a FK.

However the JDBC specification says

Retrieves a description of the foreign key columns in the given foreign key table that reference the primary key or the columns representing a unique constraint of the parent table (could be the same or a different table).

So if that was fixed it would be possible for SchemaSpy to use getCrossReference. However I need to look into our code and check if we have support for that kind of relationship.

But I think I'll keep this as an enhancement. SchemaSpy is missing the FK to index-column-that-isnt-PK feature.

The enhancement would be:

1. Support the data structure
2. Implement usage of getCrossReference()
3. Allow custom SQL to override the getCrossReference() (could be a possible optimization to avoid including PK)