On Mon, Feb 5, 2018 at 6:28 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> That is because you do not have an index on the tableB child key of the
> relation (fk).  This is required.  see the lint command in a command line
> shell near you.
>
> You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN
> b which means, in English, include all the rows of a whether or not there
> are (1 or more) matches in table b?  Therefore you require indexes on the
> lookup columns (unless your tables are very paltry == as in contain no
> rows).
>
> Yea;


> Multiple on clauses can be specified as in:
>
> create table tableB fk, datab, foreign key (fk) references tableA (pk) on
> delete cascade on update cascade);
>

My complaint on the index above is that it's not already just done.  being
that it is foreign, it MUST exist in PK of primary, and the FK would fault
if a PK changed without cascade updates because it now no longer references
a valid PK.

now if that weren't itself indexed of course it would need an index on the
child table then.


>
> You can create an index on a determinitic or slow-change function.
>

How? I can do...


// deterministic function
db.procedure( "hash", col=>"hash("+col+")" );
// non-determinstic function.
db.function( "myrandom", ()=>Math.random );

create table tableD ( a char );
insert into tableD( a) values (1),(2),(3),(4);
-- alter table tableD add column b char default (random());  --Error:
Cannot add a column with non-constant default
alter table tableD add column b char default (hash(a));  --Error: default
value of column [b] is not constant
select * from tableD;

create table tableE ( a char, b char default(random()) );
insert into tableE ( a) values (1),(2),(3),(4);
select * from tableE;

-- this can work if something like '12' or another function() is used
create table tableF( a char, b char default(hash(a)) ); -- Error: default
value of column [b] is not constant

create table tableF( a char, b char default(hash(random())) ); -- no error,
but wouldn't be 'constant' either...

insert into tableF ( a) values (1),(2),(3),(4);
select * from tableF;

(created with hash(random()) which is less constant than 'the value in
column a')

[ { a: 1, b: 'hash(3587505343419240000)' },
  { a: 2, b: 'hash(-578925135215998800)' },
  { a: 3, b: 'hash(-8823242962340456000)' },
  { a: 4, b: 'hash(1563125645423464400)' },
  { a: 1, b: 'hash(-593800428379214000)' },
  { a: 2, b: 'hash(-5699801957833211000)' },
  { a: 3, b: 'hash(-2110690358976664300)' },
  { a: 4, b: 'hash(-7312991265931917000)' },
  { a: 1, b: 'hash(-4664682697074342000)' },
  { a: 2, b: 'hash(5587846649139380000)' },
  { a: 3, b: 'hash(5464363349484015000)' },
  { a: 4, b: 'hash(-3159093798119682000)' } ]




but I can't (where hash is deterministic)
alter table table D add column c char default (hash(a)) )



>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Monday, 5 February, 2018 18:24
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Auto Index Warnings; key on deterministic functions
> >
> >I have a couple tables like...
> >
> >create table tableA ( pk PRIMARY KEY, dataA )
> >create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
> >tableA(pk) ON
> >DELETE CASCADE )
> >
> >if the table was also ON UPDATE CASCADE could it slave to the same
> >index as
> >primary key?
> >
> >doing a LEFT JOIN on the tables  I get a warning message about
> >automatic
> >index is being generated...
> >
> >-------------------
> >Separately, can a key be added to a table that is a determinstic
> >function?
> >
> >alter tableB add column dFuncB char default dFunc(dataB)
> >create INDEX dFuncIndex on tableB ( dFuncB )
> >
> >where dFunc is a deterministic function... oh wait, it can just be a
> >function... but it can't reference a column value as the source of
> >its
> >data... the expression part needs to be constant.
> >I suppose I can add triggers to the table to update the default
> >value.
> >
> >but then that function is non-constant... so how does that index
> >work?
> >
> >------------
> >Kind of a X-Y problem, that is what I thought I might like to do is
> >just
> >have a key into a table that's partial, because not all entries in
> >the
> >table will be referenced by that key... (although since it is
> >algorithmic,
> >could just be filled anyway).   But then if a could be made, the
> >value
> >wouldn't actually have to exist in the table, since I really never
> >need
> >that value, but just need to lookup by the value...
> >
> >create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
> >
> >I CAN do
> >
> >select * from tableB where dFunc(dataB) === 'some value'
> >
> >but that's not indexed at all.
> >_______________________________________________
> >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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to