I am thinking the answer to this question is “No”, but I figured I’d ask any ways.
Given only the contents of a databases’ Metadata (ie. SQLite_Master for example)… it is possible to create a ERD…this I know
but for that ERD… is there any way to determine what relations are 1->1, 1->M, M->M etc… the reason would be that the lines connecting the diagram nodes would have different end point symbols. (Crow Foot Notation)
I’m guessing that a person familiar with the database would have to “guess” and select an apporpriate connection type, on a instance by instance basis?
For sqlite I dont think you can do it 100% right
At best you have the foreign key definition (if someone has decided to include those) or references
And none of those detail cardinality of the releationship
I realize that the FK defines the connection… a subject that I have learned quite alot about in regards to SQLite in the past few days.
I was hoping there was something in the FK information that I might have missed that indicated the cardinality
Nothing that I’m aware of in the vast majority of databases indicates cardinality - just the foreign key
A long time ago there WAS a db erd editor done in RB / RS /Xojo called Xerd that could spit out sql for OpenBase, Postgresql and … I forget if it did mysql or not
I dont recall if you could pick the diagram style (chen ,martin , bachman) or not
something like this ? :
I extract all you can see from the foreign keys of the sqlite database
(and also postgres and mysql recently)
I love that screen shot (contents). Onmy questionable to my eye is the orange color (not so readable); I would use one more darker color.
Years ago, Ive drawn something similar (less nice) with the MenuBar (complete with keywork short cuts)
foreign keys only deal with one-to-many. (at least with sqlite)
many-to-many is in fact always made with an intermediate table, so is exploded into 2 one-to-many relations.
[quote=337594:@Jean-Yves Pochez]foreign keys only deal with one-to-many. (at least with sqlite)
many-to-many is in fact always made with an intermediate table, so is exploded into 2 one-to-many relations.[/quote]
I don’t think that is true
FK -> PK is many to 1
PK(fk) -> PK(fk) is 1 to 1
Pk(fk) -> FK is 1 to many
FK -> FK is many to many
we can agree to disagree
If a FK references a PK… it has to be many to 1
now true, on the FK side it might actually be 1, 0 or many and you can’t “guess” that , but the “TO 1” part is true, since PK must be unique (there can be only one)
If the FK on both sides is also a PK (which is entirely legal), then it is in fact a “1 to 1” period
There are many other commercial Schema Diagrammers that “guess” in this EXACT same manner
What is the idea to connect a FK elsewhere than to the PK?
nothing says that an FK must reference a PK
nothing says that an FK must BE a PK
Create Table t1 ( id integer, data text);
Create Table t2 ( id integer, data text);
Create Table t3 ( column1 text,
foreign Key ( column2) references t1 ( id)
On delete no action
On Update no action
foreign Key ( column3) references t2 ( id)
On delete no action
On Update no action)
That is perfectly “Legal” data structure… it may not “make sense”… but it is “legal”
the 2 FK in T3 reference NON key values in T1 and T2
OK, the physical model is legal but what about the logical one?
At least PostgreSQL does enforce a Foreign Key to reference a column that is unique:
[code]# create table a(i int, u int unique);
create table b(i int references a(i));
ERROR: there is no unique constraint matching given keys for referenced table “a”
create table b(i int references a(u));
I said “it might not make sense”… and my task is to show what the “physical” is… it is the responsibiltiy of whoever designed the database to be concerned about the logical.
I am going to display what “IS”… .not what “should be”
[quote=338979:@Dave S]nothing says that an FK must reference a PK
nothing says that an FK must BE a PK[/quote]
from the SQLite docs on the topic it seems, the example above would not be allowed by this DBMS either.
Not sure what you are trying to indicate?
Are you saying the SQLite would not allow the example I posted above? If so, I must strongly disagree… as I used SQLite to create it to begin with…
I am not saying what SHOULD be done, I am indicatating what CAN BE done.
My application needs to analyze an SQLite database, and inform the user of what IS there… again not to tell them what SHOULD or SHOULD NOT be.
The examples in those Documents are what you SHOULD do… .but SQLite imposes no constraints on what you might do
This discussion is polar opposites…
You folks (and rightfully so), and arguing that a developer should not do these things…
I am arguing that regardless, they CAN do them, and if they DO, then I will document what the database IS
So, I think we have reached an impasse,