SchemaSpy automatically detects only Foreign Keys that reference a Primary Key about schemaspy HOT 7 OPEN

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.

from schemaspy.

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

from schemaspy.

@riccardopedron96 What database is this for?

from schemaspy.

Oracle

from schemaspy.

@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.

from schemaspy.

@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);

from schemaspy.

@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)

from schemaspy.