On Mon, Feb 5, 2018 at 6:28 PM, Keith Medcalf <[email protected]> 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-
> >[email protected]] 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
> >[email protected]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
