Re: [sqlite] Foreign key child index question

2016-12-13 Thread David Raymond
] On Behalf Of Dominique Devienne Sent: Tuesday, December 13, 2016 2:50 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key child index question On Tue, Dec 13, 2016 at 8:07 PM, David Raymond <david.raym...@tomtom.com> wrote: > sqlite> create table parentTable >...> ( >

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Dominique Devienne
On Tue, Dec 13, 2016 at 8:07 PM, David Raymond wrote: > sqlite> create table parentTable >...> ( >...> id integer primary key, >...> a int not null, >...> b int not null, >...> otherStuff, >...> unique (a, b) >...> ); > > sqlite>

Re: [sqlite] Foreign key child index question

2016-12-13 Thread David Raymond
half Of Dan Kennedy Sent: Tuesday, December 13, 2016 12:05 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Foreign key child index question On 12/13/2016 11:02 PM, Paul Egli wrote: > On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin <slav...@bigfraud.org> wrote: > >> On

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Dan Kennedy
On 12/13/2016 11:02 PM, Paul Egli wrote: On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin wrote: On 13 Dec 2016, at 3:53pm, Paul Egli wrote: Well if i am missing something, i still cannot see it. Based on these quotes in the docs, i assume that a

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Simon Slavin
On 13 Dec 2016, at 4:02pm, Paul Egli wrote: > Great. Just to clarify, SQLite will already use "alternateChildIndex1" from > the example? Or just that it would be possible as an enhancement request? I don’t know the answer, but you can find out exactly what SQLite has

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Paul Egli
On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin wrote: > > On 13 Dec 2016, at 3:53pm, Paul Egli wrote: > > > Well if i am missing something, i still cannot see it. > > > > Based on these quotes in the docs, i assume that a NULL in the child > table > >

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Simon Slavin
On 13 Dec 2016, at 3:53pm, Paul Egli wrote: > Well if i am missing something, i still cannot see it. > > Based on these quotes in the docs, i assume that a NULL in the child table > means that it does not have a parent. You are correct. I missed that. So yes, the

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Paul Egli
On Tue, Dec 13, 2016 at 9:41 AM, Simon Slavin wrote: > > On 13 Dec 2016, at 3:20pm, Paul Egli wrote: > > > Why must SQLite find rows where the value is NULL? > > Because the related fields in the offspring row might have NULL in them, > and SQLite

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Simon Slavin
On 13 Dec 2016, at 3:20pm, Paul Egli wrote: > Why must SQLite find rows where the value is NULL? Because the related fields in the offspring row might have NULL in them, and SQLite needs to know how to find the parent row for that row. Mind you, if the relating key

Re: [sqlite] Foreign key child index question

2016-12-13 Thread Paul Egli
On Wed, Dec 7, 2016 at 3:11 PM, Simon Slavin wrote: > > On 7 Dec 2016, at 8:40pm, David Raymond wrote: > > > Question on making indexes for the child fields of foreign keys. I have > a child table with a number of foreign keys on fields which the

Re: [sqlite] Foreign key child index question

2016-12-07 Thread Simon Slavin
On 7 Dec 2016, at 8:40pm, David Raymond wrote: > Question on making indexes for the child fields of foreign keys. I have a > child table with a number of foreign keys on fields which the majority of the > time are null. I've currently got indexes on the child fields

[sqlite] Foreign key child index question

2016-12-07 Thread David Raymond
http://www.sqlite.org/foreignkeys.html#fk_indexes Question on making indexes for the child fields of foreign keys. I have a child table with a number of foreign keys on fields which the majority of the time are null. I've currently got indexes on the child fields for the purposes of speeding