On Tue, Feb 28, 2017 at 6:01 PM, J Decker <d3c...@gmail.com> wrote:
>
>
> 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?
>
> okay but I guess that's what on delete SETNULL is for... so you can keep
the child as a special 'NULL' record for later searching just by file....
> 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
