On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden <jklow...@schemamania.org>
wrote:
> On Tue, 28 Feb 2017 17:13:30 +0000
> Hick Gunter <h...@scigames.at> wrote:
>
> > Let's assume you have a parent table P (color, shape, style, ...)
> > that is unique on the three named fields. Let's assume you have a
> > child table C (id, ...,color, shape, ...) that references P
> > (color,shape)
>
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard. Thank you, Kees.
>
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
>
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
>
Then how would you properly find the children? Or find any information
about their parent, the child would have to in turn be deleted.
foreign keys are as good as pointers. If the parent structure pointing at
a child is no longer, how is the child valid for anything other than
garbage collection?
If the unique; (memory address) key contains multiple parts, then you have
to reference all aprts. If there was a part that was itself unique then
you wouldn't really need the second part; otherwise you do in order to
differentiate (1,1) from (1,2)
perhaps what you intend is to use a graph database, where a single 'child'
might be referred to from mulitple parents (links) ; but then your model
should be something more self recursive like...
(pseudoSQL)
create table inode( iNode char PRIMARY KEY, moreColumns char, )
create table inodeLinks( someInode char, someOtherInode char, FK(someinode
references inode.iNode), FK(someOtheriNode references inode.iNode) )
create table inodeFileData( iNode char , fileDataIfAny char, )
create table inodeNameData( iNode char, theNameOfThisPoint char, )
create table inodeFileData( iNode char, attributes char, )
Such that any directory might also contain some data, but any file can
contain other files since each thing is named separatly from it's key.
Instead of having the file records themselves having their directory, why
not just make a separate table
( directory char , hasFiles char );
CTE queries can make this even tolerable for linear queries
--------------------
Sorry I wandered into an alternative solution instead of the other
representation...
in .NET you have DataSet, whichi contains DataTables, and between DTs are
Relation(s). When you fill a dataset with all properly keyed things, the
row in one table (files).getChildRows( "filesInDirectory" ) (returns more
rows)
And it's really a direct reference, it doesn't have to go additionally
search through the index in the child table to get to the rows by looking
up a key, it just points to it. Much like working with a graph, your
master records just point at the children, and you never have to search for
anything...
> delete from P where not exists (
> select 1 from C where color = P.color and shape = P.shape );
>
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>
> delete from C where exists (
> select 1 from P where color = C.color and shape = C.shape
> and /* ... P criteria ... */
> );
>
> From a theoretical standpoint, for relations
>
> P{A,B}
> and
> C{C,B} with C{B} referencing P{B}
>
> without loss of information we may add
>
> B{B}
>
> and constraints
>
> P{B} references B{B} and C{B} references B{B}
>
> But, having added B, we have not added any information. We have
> merely added another relation that is the projection of P{B}.
>
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them. Maybe that, or
> practical considerations, or both, motivated the SQL rule.
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
