#15603: ER Modeler not generating the Foreign Key when there is parent/child(source/destination) relationship

alonsomendez reported 2018-06-25T16:56:59Z · last modified 2018-07-11T23:10:02Z

ER Modeler not generating the Foreign Key when there is parent/child(source/destination) relationship

Dev	tomconrad
QA	nhilam

Priority	Low
Complexity	Unknown

Component	Tools - ER Modeler
Version	19.0

Here are 2 issue regarding the ER Modeler while working on a HP-Vertica8 model.

1) creating the DDL-Skript from a table with foreign key constraints does not contain the foreign key constraints belonging to this table as one would expect.
Instead foreign key constraints from other tables to the selected table are beeing created, which is completly confusing and (as we see it) wrong!

Here a 2 scripts generated and screenshots to show what the problem is:

/*
Skript generiert von Aqua Data Studio 19.0.1-2 am Jun-19-2018 10:57:15 AM
Datenbank: dwhE2
Schema: dwh
*/

CREATE TABLE "dwh"."T_LIZPOS_STATUS" (
"LIZENZNUMMER" varchar(10) NOT NULL,
"LIZENZPOSITION" numeric(5,0) NOT NULL,
"STAT_SCHEMA" varchar(20) NOT NULL,
"STATUS" varchar(20) NOT NULL,
"STAT_DATUM" date NOT NULL,
"STAT_AKTIV_KNZ" varchar(1),
"STAT_UNAME" varchar(50),
"DWH_INSERT_DT" timestamp(0),
"DWH_UPDATE_DT" timestamp(0),
"DWH_LOESCH_KNZ" varchar(1),
"DWH_MD5HASH" varchar(32),
CONSTRAINT "LIZPSTAT_STAT_PK" PRIMARY KEY("STATUS","STAT_DATUM","STAT_SCHEMA","LIZENZPOSITION","LIZENZNUMMER")
);
/* foreign key constraints missing here !!! */

Content of generated DDL-Skript (red part not expected here!):

/*
Skript generiert von Aqua Data Studio 19.0.1-2 am Jun-19-2018 11:11:34 AM
Datenbank: dwhE2
Schema: dwh
*/

CREATE TABLE "dwh"."T_LIZENZPOSITIONEN" (
"LIZENZNUMMER" varchar(10) NOT NULL,
"LIZENZPOSITION" numeric(5,0) NOT NULL,
"TAR_ID" varchar(50),
"TARGRP_KBEZ" varchar(20),
"NUTZUNG_BEGINN" date,
"NUTZUNG_ENDE" date,
"SAISON_BEGINN" date,
"SAISON_ENDE" date,
"STO_ERF_UNAME" varchar(50),
"STO_ERF_REFTAB" varchar(30),
"STO_ERF_REFCODE" varchar(80),
"STORNODATUM" date,
"DWH_INSERT_DT" timestamp(0),
"DWH_UPDATE_DT" timestamp(0),
"DWH_LOESCH_KNZ" varchar(1),
"DWH_MD5HASH" varchar(32),
CONSTRAINT "LIZPOS_PK" PRIMARY KEY("LIZENZPOSITION","LIZENZNUMMER")
);

ALTER TABLE "dwh"."T_LIZPOS_STATUS"
ADD CONSTRAINT "LIZPSTAT_LIZPOS_FK"
FOREIGN KEY("LIZENZNUMMER", "LIZENZPOSITION")
REFERENCES "dwh"."T_LIZENZPOSITIONEN"("LIZENZNUMMER", "LIZENZPOSITION");

The screenshots attached are the options and the tables involved in this issue.

2) direct Database import of a table with foreign key constraints does also not import these fk constraints!

This looks like it is a bug in the ADS logic. The modeler logic assumes a parent/child(source/destination) relationship where the parent is the referenced table. This means that the constraint should be always be on the child table. Therefore, when the parent table is selected when generating the ddl, it should not script the constraint. Conversely, when the child table is selected, the constraint should get scripted.

The fix should include a change to ERScriptGeneratorThread->run.

// relations

for (DiagramElement em : objects) {

if (ETable.is(em)) {

ETable t = (ETable) em;

for (ERelation r : t.getRelations()) {

if (r.getDstTable() == t) {

scriptCREATE(r);

}

Support Information:

Product: Aqua Data Studio
Version: 19.0.1-2
Build #: 55841
Build Date: 2018-May-18 03:04:57 PM

Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / de / DE / Oracle Corporation 1.8.0_102-b14
Memory: Max=954.728.448; Total=678.428.672; Free=461.361.968; CPUs=4

In-Window Graphics Capabilities
Graphics Vendor: Intel
OpenGL Renderer: Intel(R) HD Graphics 4400
OpenGL Version: 4.3.0 - Build 10.18.14.4432
Double-Buffering: Enabled
Anti-Aliasing: Enabled
Anti-Aliasing Sample Count: 8
Hardware Acceleration: Enabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 8
Depth Bits: 16
Accumulation Buffer Bits: Red: 0 Green: 0 Blue: 0
Initialization Time: 1847 ms

Offscreen Graphics Capabilities
Graphics Vendor: Brian Paul
OpenGL Renderer: Mesa OffScreen
OpenGL Version: 2.1 Mesa 7.8.2
Double-Buffering: Disabled
Anti-Aliasing: Disabled
Anti-Aliasing Sample Count: 0
Hardware Acceleration: Disabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 8
Depth Bits: 16
Accumulation Buffer Bits: Red: 16 Green: 16 Blue: 16
Initialization Time: 67 ms
Hardware PBuffer Available: No
Using PBuffer: No
Using Ram Buffer: Yes
Offscreen Rendering: Enabled
Offscreen Buffer Size: 800x600

4 attachments

TestFK1.xed

2018-06-25T19:49:18Z

2 KB
TestFK.xed

2018-06-25T19:49:18Z

3 KB
TestFK2.xed

2018-06-25T19:49:18Z

2 KB
TestFK3.xed

2018-06-25T19:49:18Z

3 KB

All Comments (9) Change History

tomconrad 2018-06-25T19:53:02Z · (edited)

19.0 SVN# 55881

19.5 SVN# 55882

Some test cases for different databases attached.

nhilam 2018-06-26T00:02:37Z

Verified in ADS 19.0.1-3 and ADS 19.5.0-dev-4.

tomconrad 2018-07-02T19:57:38Z · (edited)

Hi Alonso,

The patch can be downloaded from

Patch: http://www.aquafold.com/download/v19.0.0/ads-19.0.1-3-patch.zip

Update Instructions: http://www.aquafold.com/support-update.html#v19

Thanks,

Tom

alonsomendez 2018-07-06T16:41:54Z

After applying the patch the customer came back with the following bugs:

1 ) Script generation in the preview window does create only constraints for the selected table(s). But it also drops constraints from other tables. Here is an example:

ALTER TABLE "dwh"."T_LIZENZEN"

DROP CONSTRAINT "LIZ_LIZ_FK_1";

ALTER TABLE "dwh"."T_LIZENZPOSITIONEN"

DROP CONSTRAINT "LIZPOS_LIZ_FK_1";

ALTER TABLE "dwh"."T_LIZ_STATUS"

DROP CONSTRAINT "LIZSTAT_LIZ_FK";

DROP TABLE "dwh"."T_LIZENZEN";

CREATE TABLE "dwh"."T_LIZENZEN" (

"LIZENZNUMMER" varchar(10) NOT NULL,

"VORGART_KBEZ" varchar(30),

"PARTNER_ID_LIZENZNEHMER" varchar(30),

"PARTNER_ID_ZUST_GRUPPE" varchar(30),

"ERFASSER_UNAME" varchar(50),

"ERF_REFCODE" varchar(30),

"ERF_REFTAB" varchar(30),

"VLZTYP_REFTAB" varchar(30),

"VLZTYP_REFCODE" varchar(30),

"ZAHLWEISE_REFTAB" varchar(30),

"ZAHLWEISE_REFCODE" varchar(30),

"ANLAGE_DT" date,

"VERTRAGSBEGINN" date,

"VERTRAGSENDE" date,

"VORGAENGER_LIZNR" varchar(10),

"DWH_INSERT_DT" timestamp(0),

"DWH_UPDATE_DT" timestamp(0),

"DWH_LOESCH_KNZ" varchar(1),

"DWH_MD5HASH" varchar(32),

CONSTRAINT "LIZ_PK" PRIMARY KEY("LIZENZNUMMER")

);

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_LIZ_FK_1"

FOREIGN KEY("VORGAENGER_LIZNR")

REFERENCES "dwh"."T_LIZENZEN"("LIZENZNUMMER");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_VORGART_FK"

FOREIGN KEY("VORGART_KBEZ")

REFERENCES "dwh"."T_SVORGANGSARTEN"("VORGART_KBEZ");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_GP_LIZENZNEHMER_FK"

FOREIGN KEY("PARTNER_ID_LIZENZNEHMER")

REFERENCES "dwh"."T_SGESCHAEFTSPARTNER"("PARTNER_ID");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_GP_ZUST_GRUPPE_FK"

FOREIGN KEY("PARTNER_ID_ZUST_GRUPPE")

REFERENCES "dwh"."T_SGESCHAEFTSPARTNER"("PARTNER_ID");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_ERF_REFCOD_FK"

FOREIGN KEY("ERF_REFCODE", "ERF_REFTAB")

REFERENCES "dwh"."T_SREF_CODES"("REFCODE", "REFTAB_ID");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_VLZTYP_REFCOD_FK"

FOREIGN KEY("VLZTYP_REFTAB", "VLZTYP_REFCODE")

REFERENCES "dwh"."T_SREF_CODES"("REFTAB_ID", "REFCODE");

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_ZW_REFCOD_FK"

FOREIGN KEY("ZAHLWEISE_REFTAB", "ZAHLWEISE_REFCODE")

REFERENCES "dwh"."T_SREF_CODES"("REFTAB_ID", "REFCODE");

2) Script generation to file for the same table generates fk-constraints of other tables (This is the same table as the example before).

/*

Skript generiert von Aqua Data Studio 19.0.1-3 am Jul-03-2018 11:57:38 AM

Datenbank: dwhE2

Schema: dwh

*/

CREATE TABLE "dwh"."T_LIZENZEN" (

"LIZENZNUMMER" varchar(10) NOT NULL,

"VORGART_KBEZ" varchar(30),

"PARTNER_ID_LIZENZNEHMER" varchar(30),

"PARTNER_ID_ZUST_GRUPPE" varchar(30),

"ERFASSER_UNAME" varchar(50),

"ERF_REFCODE" varchar(30),

"ERF_REFTAB" varchar(30),

"VLZTYP_REFTAB" varchar(30),

"VLZTYP_REFCODE" varchar(30),

"ZAHLWEISE_REFTAB" varchar(30),

"ZAHLWEISE_REFCODE" varchar(30),

"ANLAGE_DT" date,

"VERTRAGSBEGINN" date,

"VERTRAGSENDE" date,

"VORGAENGER_LIZNR" varchar(10),

"DWH_INSERT_DT" timestamp(0),

"DWH_UPDATE_DT" timestamp(0),

"DWH_LOESCH_KNZ" varchar(1),

"DWH_MD5HASH" varchar(32),

CONSTRAINT "LIZ_PK" PRIMARY KEY("LIZENZNUMMER")

);

ALTER TABLE "dwh"."T_LIZENZEN"

ADD CONSTRAINT "LIZ_LIZ_FK_1"

FOREIGN KEY("VORGAENGER_LIZNR")

REFERENCES "dwh"."T_LIZENZEN"("LIZENZNUMMER");

ALTER TABLE "dwh"."T_LIZENZPOSITIONEN"

ADD CONSTRAINT "LIZPOS_LIZ_FK_1"

FOREIGN KEY("LIZENZNUMMER")

REFERENCES "dwh"."T_LIZENZEN"("LIZENZNUMMER");

ALTER TABLE "dwh"."T_LIZ_STATUS"

ADD CONSTRAINT "LIZSTAT_LIZ_FK"

FOREIGN KEY("LIZENZNUMMER")

REFERENCES "dwh"."T_LIZENZEN"("LIZENZNUMMER");

After applying the patch the customer came back with the following bugs:

tomconrad 2018-07-06T17:43:41Z

Hi Alonso,

I fixed it for that specific case but it appears that there are other area's of the code that need to get changed. Will investigate and see if we can do a complete fix in a patch.

Thanks, Tom

tomconrad 2018-07-06T20:47:11Z

19.0 SVN# 55891

19.5 SVN# 55892

nhilam 2018-07-10T01:41:12Z

Verified in ADS v19.0.1-5 and ADS v19.5.0-dev-5

tomconrad 2018-07-11T19:11:39Z

Hi Alonso,

The new patch can be downloaded from

Patch: http://www.aquafold.com/download/v19.0.0/ads-19.0.1-5-patch.zip

Update Instructions: http://www.aquafold.com/support-update.html#v19

Please have them give that a try!!

Thanks,

Tom

nhilam 2018-07-11T23:10:02Z

Hi Alonso,

The fix has been deployed. Please have the customer download the fix from here: http://downloads.aquafold.com/v19.0.0.

Search Tips

Aqua Data Studio / nhilam

ER Modeler not generating the Foreign Key when there is parent/child(source/destination) relationship

4 attachments

Issue #15603

Completion

Search Tips

Aqua Data Studio / nhilam

Title

ER Modeler not generating the Foreign Key when there is parent/child(source/destination) relationship

4 attachments

Issue #15603

Completion