Database Self-referencing foreign key bug in visio 2000

[Michel]

Hello,

I did a reverse engeneering for both Oracle and MSAcces of
a table that have a self referencing foreign key.
When I check the resulting model, visio 2000 gives an
error L3022 "Self-referencing foreign key column 't.b'
cannot exist as both a parent and a child in the same
relationship".
IMHO that error L3022 is a visio 2000 bug.

I created a table in access and Oracle and try the reverse
engeneering from both with the same problem.
create table t (
a integer not null,
b integer not null,
c integer not null,
constraint t_pk primary key (a, b),
constraint t_fk1 foreign key (c, b)
references t (a, b)
);
The c field is a reference to a parent record that have
the same b field in the same table.

Thank in advance for your comments.
Can someone reproduce my test with a more recent version
of visio ?

 

[Kevin Nechodom]

I used Visio for Enterprise Architects 10.0.5110, VS.NET
2003, and I got exactly the same error. But I do not
consider it a bug. It is telling you that there as GOT to
be another way of doing this that doesn't torture your
data.

Basically, you are testing for a value to see if it is
there, which will succeed, because you put it there. How is
it useful?

Kevin

Kevin Nechodom
University of Utah MBM

 

[Guest]

You seem to have missed the word "parent" in my
explanations.
Imagine that
- a represent a directory name
- b is a disk drive
- c is the parent directory name.

a b c
record 1 \ C: \
record 2 WINNT C: \
record 3 SYSTEM32 C: ZINNT <- must be refused by
db engine because there is no record with ZINNT in the
table for the C: Drive.

Michel

 

[Kevin Nechodom]

Actually, I DO understand the idea of a parent-child
relationship within
a table. But your example is not doing what you want.

Using your example, there is no reason to have b (disk
drive) participate
in your foreign key relationship, which is what Visio is
telling you.

If you wish for this to happen, then set up your table as
follows:
ParentDisk
ParentDir
ChildDisk
ChildDir
Primary Key: ParentDisk, ParentDir
Foreign Key ChildDisk, ChildDir referencing ParentDisk,
ParentDir

The check that it appears that you are trying to do, make
sure that you
don't cross-link directories across disk drives, is not
being represented
correctly in the method you are trying.

Even worse, this will not handle cases where the same name
is being
used as subdirectories. Look at the following case:

C:\
- Apps
--- App1
----- bin
----- config
--- App2
----- log
- Data
--- App1
----- archive
--- App2
----- archive

When you want to insert the representation of a new
directory:
C:\Data\App1\archive\2004

how would you do it? If you send:
"archive","C:","2002"

does it go under App1 or App2? If you test:
"App1","C:","bin"

should it succeed, because that is valid under C:\Apps, or
should it fail, because C:\Data\App1 does not have a bin
directory?

So, you can either 1) just drop the constraint of the disk
drive,
2) use the method I suggested above but add full pathing,
or
3) blow your mind with a radically different method of
representing hierarchical data in a database given in this
tutorial:
http://www.sitepoint.com/article/1105/2/session

This method is oriented towards representing a tree, rather
than filling it.
Nevertheless, you can use the ideas to build tests with
stored procedures.

Good luck!

Kevin

Kevin Nechodom
University of Utah MBM

 

[Michel]

Please consider my model as correct. My example with
directories was not very clever... My post is about visio,
not about database design and we are going out of scope ;-)

This design is accepted by 2 database engines.
Visio was able to reverse engeneer that design for both DB.
But it gives an error while checking it.
Is it a visio bug ?
Does visio 2003 react the same way ?

Here is the SQL for creating the table:
create table t (
a integer not null,
b integer not null,
c integer not null,
constraint t_pk primary key (a, b),
constraint t_fk1 foreign key (c, b)
references t (a, b)
);

Thanks.

Michel.