Re: [sqlite] Foreign keys - one to one, one to many relationship

Am Sat, 14 Oct 2017 13:01:59 -0400 schrieb Richard Damon: > you could create a record in the other table without a corresponding > record in the blob table, but that is sort of needed to avoid chicken > and egg issues with creating new records (not sure when foreign key > requirements are

Re: [sqlite] Foreign keys - one to one, one to many relationship

On Saturday, 14 October, 2017 04:37, Richard Damon , wrote: >One way to make them build one to at-most-one relationships is too >add a unique index on the foreign key, then it can only occur once. >A True one-one relationship by the normailization rules says that >they

Re: [sqlite] Foreign keys - one to one, one to many relationship

Yes, sometime for performance reasons you need to break the rules of normalization. I did think that if the blob was at the end of the record, sqlite wouldn't read that part of the record unless asked to (so you aren't gaining that much with the separate table). If the second table is just

Re: [sqlite] Foreign keys - one to one, one to many relationship

In this case, rules can be made to be broken. In the case of larger blobs, I push those out to a different table, and enforce (Through my software, not a unique constraint as mentioned) the one-to-one relationship. There is no reason to keep the blob on a record that is queried constantly. On

Re: [sqlite] Foreign keys - one to one, one to many relationship

On 10/14/17 5:35 AM, Simon Slavin wrote: On 14 Oct 2017, at 10:30am, Csányi Pál wrote: With which constraints can one create a foreign key to first: get one to one relationship, and second: one to many relationship? Foreign keys always implement a one-to-many

Re: [sqlite] Foreign keys - one to one, one to many relationship

On 14 Oct 2017, at 10:30am, Csányi Pál wrote: > With which constraints can one create a foreign key to > first: get one to one relationship, and > second: one to many relationship? Foreign keys always implement a one-to-many relationship. You could enforce a one-to-one

Re: [sqlite] Foreign keys & TEMPORARY tables.

On 11/7/16, Smith, Randall wrote: > It's quite common to create some kind of large database table or > constellation of tables that will be used for intermediate work product, and > which will be deleted when an operation, or the database session, is > complete. This

Re: [sqlite] foreign keys

On Fri, 25 Apr 2014 09:22:20 +0100 (BST), olivier Ménard wrote: > Hello > > I tried, (with sqlite3 command line) : > > create table T(a primary key not null); > create table U(a references T not null); Shouldn't that be: create table U(a not null references T(a)); ? >

Re: [sqlite] foreign keys

Ménard; General Discussion of SQLite Database > Subject: Re: [sqlite] foreign keys > > On Fri, Apr 25, 2014 at 10:22 AM, olivier Ménard <men37...@yahoo.fr> > wrote: > > create table T(a primary key not null); create table U(a references T > > not null); insert into U valu

Re: [sqlite] foreign keys

On Fri, Apr 25, 2014 at 10:22 AM, olivier Ménard wrote: > create table T(a primary key not null); > create table U(a references T not null); > insert into U values(4) C:\Users\DDevienne>sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected

Re: [sqlite] Foreign keys needing an index

Simon Slavin wrote: > Need someone more familiar with the design philosophy and source code than I > am (which is not at all). > > > > Says you need an index for anything which acts as a parent. I can understand > why

Re: [sqlite] Foreign keys needing an index

On Fri, Nov 30, 2012 at 02:57:30PM +, Simon Slavin scratched on the wall: > Need someone more familiar with the design philosophy and source code than I > am (which is not at all). > > > > Says you need an index for anything which acts as

Re: [sqlite] foreign keys across database boundaries

On Mon, 27 Aug 2012 11:44:18 +0300, Baruch Burstein wrote: > Can foreign keys or triggers be set between ATTACHed database tables? If > so, is there any other type of statment that doesn't work across ATTACH > boundaries? Foreign keys, triggers and views cannot cross

Re: [sqlite] foreign keys across database boundaries

On 08/27/2012 03:44 PM, Baruch Burstein wrote: Can foreign keys or triggers be set between ATTACHed database tables? No. They can not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys bypassed on insert?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/24/2011 02:03 PM, David L wrote: > This can't be the desired behaviour, can it? It isn't. The team have fixed the problem: http://www.sqlite.org/src/timeline Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment:

Re: [sqlite] Foreign keys bypassed on insert?

On Sun, 24 Apr 2011 23:03:03 +0200, "David L" wrote: >Hello Sqlite-users! > >I have stumbled upon the fact that foreign keys are not always enforced. > >Specifically, that happened when I used the INSERT statement with a SELECT >clause, which apparently caused invalid values

Re: [sqlite] Foreign keys and Temp Tables

On Feb 9, 2010, at 8:54 AM, Paul Vercellotti wrote: > > > Hi there, > > Are there any restrictions in SQLite on foreign key references in > temporary tables? Is referential integrity enforced between temp > tables and persistent ones? (That is, does the referential integrity > checking

Re: [sqlite] Foreign keys + NATURAL JOIN

Kristoffer Danielsson wrote: > Thanks. > > This leads me to the next question. > > Why does the statement below yield a cartesian product? > > SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw! It doesn't. In fact "t1 NATURAL JOIN t1" would do the exact opposite, because *all* of the

Re: [sqlite] Foreign keys + NATURAL JOIN

sers@sqlite.org > Subject: Re: [sqlite] Foreign keys + NATURAL JOIN > > Kristoffer Danielsson wrote: > > Q: Does foreign keys affect NATURAL JOINs? > > > > I haven't tested this. Does this NATURAL JOIN produce an inner join or a > > cartesian product? > > The pre

Re: [sqlite] Foreign keys + NATURAL JOIN

Kristoffer Danielsson wrote: > Q: Does foreign keys affect NATURAL JOINs? > > I haven't tested this. Does this NATURAL JOIN produce an inner join or a > cartesian product? The presence of foreign key constraints has no effect on the results of any queries, using natural joins or otherwise. A

Re: [sqlite] Foreign keys question

> No performance gain for joins or anything like that. Using FK > constraints does not affect SELECT performance. They slow down > some INSERT/UPDATE/DELETE operations though. Thank you! I've done some timings as well (it takes a few hours to build a sizeable database) and found out pretty much

Re: [sqlite] Foreign keys question

On Oct 16, 2009, at 3:08 AM, Dennis Volodomanov wrote: > Dan Kennedy wrote: >> I don't think the triggers you are using can be implemented using >> foreign keys. Your triggers are basically reference counting (or >> garbage collecting, whatever you want to call it) - "when the >> number of

Re: [sqlite] Foreign keys question

Dan Kennedy wrote: > I don't think the triggers you are using can be implemented using > foreign keys. Your triggers are basically reference counting (or > garbage collecting, whatever you want to call it) - "when the > number of references to a data item drops to zero, delete the > data item". >

Re: [sqlite] Foreign keys question

On Oct 15, 2009, at 10:30 AM, Dennis Volodomanov wrote: > Hello all, > > I'm implementing the new FOREIGN KEY support in our database and I > have this small problem/question. > > Before I had triggers to take care of maintaining deletion of data > that's not referenced by any records, but I

Re: [sqlite] Foreign Keys and Indexes

> s.breith...@staubli.com wrote: > > So when I have a query over serveral tables normally the primay key > > is set as index when the table is joined. > > Only if you actually join on primary key column(s). Doing so is common, > but not mandatory. > > > If I need to add a where > > clause > >

Re: [sqlite] Foreign Keys and Indexes

s.breith...@staubli.com wrote: > So when I have a query over serveral tables normally the primay key > is set as index when the table is joined. Only if you actually join on primary key column(s). Doing so is common, but not mandatory. > If I need to add a where > clause > to a field of the

Re: [sqlite] Foreign Keys and Indexes

> s.breith...@staubli.com wrote: > > Now I noticed that the speed of selects are much faster with fields > > that are indexed > > Well, that's pretty much the point of having an index in the first > place. Why do you find this fact surprising? Because the indexed field that improved my program

Re: [sqlite] Foreign Keys and Indexes

s.breith...@staubli.com wrote: > Now I noticed that the speed of selects are much faster with fields > that are indexed Well, that's pretty much the point of having an index in the first place. Why do you find this fact surprising? > I´ve read that only one index is used per where (I think join

Re: [sqlite] Foreign Keys

Andy Allord wrote: I am new to SQLite 3 and I am wondering if there is a way to work with foreign keys and set handling to cascade? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys

Am 29.12.2007 um 17:00 schrieb Kees Nuyt: Better try it in the sqlite3 command line tool. That's common ground to everyone of us. Maybe no problem in the sqlite api. I will build create table scripts on the fly from XML via XSLT, that way I could also create the triggers. Second

Re: [sqlite] Foreign keys

On Sat, 29 Dec 2007 15:17:27 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: > >Am 29.12.2007 um 13:59 schrieb Kees Nuyt: > >> >> Hi Lothar, >> >> On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens >> <[EMAIL PROTECTED]> wrote: >> >> The only implementation I'm aware of is the one using

Re: [sqlite] Foreign keys

Am 29.12.2007 um 13:59 schrieb Kees Nuyt: Hi Lothar, On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: The only implementation I'm aware of is the one using triggers, but the creation is not implemented as an extension.

Re: [sqlite] Foreign keys

Hi Lothar, On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: >Hi all, > >now I have got more information on how to implement foreign key support. > >My plan is to use extension functions to do it by parsing the system >table's sql statement and returning >a result

Re: [sqlite] Foreign Keys

Parsing is a language term. You might recall being taught to parse a sentence into subject and predicate, then phrases, verbs nouns, articles etc. A computer language parser does the same thing, lexical analysis to extract the words and syntactical analysis to match to the grammar. That

RE: [sqlite] foreign keys? (sqlite3)

Re: I've created a class that generates the necessary sql statements to describe the primary-foreign key relationship between two tables. Todd, I suspect there would be interest to some on the list. You may also want to review Cody Pisto's utility linked from page:

RE: [sqlite] foreign keys? (sqlite3)

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've created a class that generates the necessary sql statements to describe the primary-foreign key relationship between two tables. This code is fairly rudimentary so far... Q: Is this interesting to anyone on this list? Q: If so, where should I

Re: [sqlite] foreign keys? (sqlite3)

* Griggs, Donald <[EMAIL PROTECTED]> [0246 18:46]: > > > -Original Message- > > From: Dick Davies [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, February 01, 2005 3:52 PM > > To: SQLite > > Subject: [sqlite] foreign keys? (sqlite3) > > > > > > The docs seem to say sqlite3 does'nt support

RE: [sqlite] foreign keys? (sqlite3)

> -Original Message- > From: Dick Davies [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 01, 2005 3:52 PM > To: SQLite > Subject: [sqlite] foreign keys? (sqlite3) > > > The docs seem to say sqlite3 does'nt support foreign keys, is > that correct? > > If so, I don't understand what

RE: [sqlite] foreign keys? (sqlite3)

Sqlite parser DOES parse foreign key constraints. Try this: create table t1( id integer, id1 integer, id2 integer, id3 integer, foreign key (id1,id2) references anotherTable(id,id1), foreign key (id3) references somethingElse(id) ); pragma foreign_key_list(t1); However, this does not mean that