Comments (7)
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:
- Support the data structure
- Implement usage of
getCrossReference()
- Allow custom SQL to override the
getCrossReference()
(could be a possible optimization to avoid including PK)
from schemaspy.
Related Issues (20)
- Cant get line breaks in Table Comments to work. HOT 6
- I am unable to get comments
- Oracle database - null password given HOT 5
- Driver Error HOT 7
- Failed to parse [schema].meta.xml HOT 5
- Terminating null reference error when generating HTML for Constraints MSSQL HOT 1
- SchemaSpy ERD (relationship tab) is not showing lines between tables HOT 4
- Relationship between Unique Keys and Foreign Key HOT 8
- ERROR - Bad config Schema (-s/-schemas) was not provided and unable to deduce schema, ... HOT 2
- Trying to set trustServerCertificate true in schemaspy.connprops Property in config file does not work HOT 2
- Getting an Exception when running against Oracle database in charset ISO-8859-15 HOT 2
- Feature request: "Comment" as title in relationships.html
- Get `AbstractMethodError` when connect to SQL Server by using `mssql17-jtds` driver. HOT 2
- Snowflake connection with private key pair HOT 4
- [Informix] Query to long when execute selectRoutineParametersSql HOT 3
- SQL/MED support (in my case PostgreSQL Foreign Data Wrappers) HOT 7
- Killed by OOM killer when analysing a complex schema HOT 2
- Live website? HOT 4
- Hyperlink fix on documents
- Tables with characteres not get generate within docs HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from schemaspy.