Re: [sqlite] Foreign Key constraint fails due to unrelated field

On Monday, 25 February, 2019 05:43, Jonathan Moules wrote: >CREATE TABLE error_codes ( > error_code INTEGER PRIMARY KEY >UNIQUE, > error TEXT >); You do not need (and should not) specify BOTH "PRIMARY KEY" and "UNIQUE". Both are enforced with a unique ind

Re: [sqlite] Foreign Key constraint fails due to unrelated field

Thanks Clemens, that was it (the comma). That was a mildly embarrassing oversight. Thanks again, Jonathan On 2019-02-25 12:52, Clemens Ladisch wrote: Jonathan Moules wrote: UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE content_hash = '0027f2c9b80002a6'; This fails

Re: [sqlite] Foreign Key constraint fails due to unrelated field

Jonathan Moules wrote: > UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE > content_hash = '0027f2c9b80002a6'; This fails because "3 and exp_content_type='ogc_except'" is interpreted as a boolean expression. To update multiple fields, separate them with commas: UPDATE l

[sqlite] Foreign Key constraint fails due to unrelated field

Hi List, I'm seeing some oddness with Foreign Keys and was wondering what was going on. A few days ago I did a refactor of my error codes, changing the numbers to be more logically consistent with groupings. They're in a separate table table which is referenced from a lookups table. This was

Re: [sqlite] FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

On Fri, 14 Dec 2018 15:05:29 +, Morten Joergensen wrote: > Hi, > > I have two tables, the second with a FOREIGN KEY - ON DELETE > CASCADE to the primary key on the first table. When I do a > transaction inserting a lot of records into the second table > at once, I am allowed to insert record

[sqlite] FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

Hi, I have two tables, the second with a FOREIGN KEY - ON DELETE CASCADE to the primary key on the first table. When I do a transaction inserting a lot of records into the second table at once, I am allowed to insert records that do not fulfill the constraint, i.e. having values in the foreign

Re: [sqlite] Foreign Key error

I thought this was that index index,sqlite_autoindex_ option4_name_1,option4_name,3, but maybe that's the unique on name. I see; I guess it is missing; indexes get created now more properly. I deleted it and recreated it, which created the indexes more properly. thanx, and sorry for the noise.

Re: [sqlite] Foreign Key error

J Decker wrote: > CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name` > varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE) > FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`) > foreign key mismatch - "option4_map" referencing "option4_name" name_id must be the pri

Re: [sqlite] Foreign Key error

test.db https://drive.google.com/open?id=1gX4QDLy3rA1YVFXZnhj_vlAClVmrU4Cz SQLite version 3.9.2 2015-11-02 18:31:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values ('5e16eb63-946e-11e8-8745-2c6fc90d3020

Re: [sqlite] Foreign Key error

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf wrote: > > >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", > >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", > >"SQLITE_DEFAULT_FOREIGN_KEYS=1" > > >Is there something about the combination of options I've used? > > Do you get differen

Re: [sqlite] FOREIGN KEY allows INSERT where it should not

ssage- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kühne, Tobias Sent: Friday, February 02, 2018 3:27 AM To: 'sqlite-users@mailinglists.sqlite.org' Subject: [sqlite] FOREIGN KEY allows INSERT where it should not Hello, maybe I am missing somet

[sqlite] FOREIGN KEY allows INSERT where it should not

Hello, maybe I am missing something, but the example you give on foreign keys allows insert where in my opinion it should clearly not. Am I missing something? Built sqlite3 binary myself on 'CYGWIN_NT-6.1 local 2.3.1(0.291/5/3) 2015-11-14 12:44 x86_64 Cygwin'. See the shell script to reproduce:

Re: [sqlite] Foreign key lint has issues with without rowid

Thanks for reporting this. Now fixed here: http://www.sqlite.org/src/info/5771b1d611b3562e Dan. On 12/01/2017 02:02 AM, David Raymond wrote: I’m using the CLI’s .lint fkey-indexes command, and it appears to be having issues with without rowid tables. A low priority thing to look into whe

Re: [sqlite] Foreign key lint has issues with without rowid

3.21.0 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, November 30, 2017 2:06 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key lint has issues with without rowid On 30 Nov 2017, at 7

Re: [sqlite] Foreign key lint has issues with without rowid

On 30 Nov 2017, at 7:02pm, David Raymond wrote: > I’m using the CLI’s .lint fkey-indexes command, and it appears to be having > issues with without rowid tables. A low priority thing to look into when > someone's bored. Please tell us which version of SQLite you’re using. You should see it

[sqlite] Foreign key lint has issues with without rowid

I’m using the CLI’s .lint fkey-indexes command, and it appears to be having issues with without rowid tables. A low priority thing to look into when someone's bored. sqlite> create table parent (id integer primary key); sqlite> create table child1 (id integer primary key, parentID int referenc

Re: [sqlite] Foreign key help

Good stuff Keith. One to archive. From: sqlite-users on behalf of Keith Medcalf Sent: Tuesday, November 28, 2017 4:02:35 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key help And of course in the command line shell you can and should use .lint

Re: [sqlite] Foreign key help

And of course in the command line shell you can and should use .lint fkey-indexes to let you know if you are missing any indexes required for efficient foreign-key enforcement operations. It will report missing indexes on the PARENT (table/columns referred to) and on CHILDREN (tables/columns

Re: [sqlite] Foreign key help

Thanks David / Simon. From: sqlite-users on behalf of Simon Slavin Sent: Tuesday, November 28, 2017 3:36:51 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key help On 28 Nov 2017, at 3:26pm, x wrote: > If I have foreign keys in place but alw

Re: [sqlite] Foreign key help

mailinglists.sqlite.org] On Behalf Of x Sent: Tuesday, November 28, 2017 10:27 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Foreign key help If I have foreign keys in place but always have foreign_keys = OFF then one day start SQLite with foreign_keys = ON what happens? Does SQL

Re: [sqlite] Foreign key help

On 28 Nov 2017, at 3:26pm, x wrote: > If I have foreign keys in place but always have foreign_keys = OFF then one > day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly > check all foreign keys and report / delete violations or does it leave > everything as is and just en

[sqlite] Foreign key help

If I have foreign keys in place but always have foreign_keys = OFF then one day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check all foreign keys and report / delete violations or does it leave everything as is and just enforce foreign keys from that point on? __

Re: [sqlite] Foreign key mismatch with ON DELETE clauses

On 26 Jul 2017, at 1:15pm, Bubu Bubu wrote: > Yeah, I took a closer look to my script today (fresh mind), and indeed > there was a typo. I've been on it so closely yesterday that I couldn't see > this huge error. > > I'm sorry, to all the mailing list, for such an useless post... Don’t worry

Re: [sqlite] Foreign key mismatch with ON DELETE clauses

Hi Dan, Yeah, I took a closer look to my script today (fresh mind), and indeed there was a typo. I've been on it so closely yesterday that I couldn't see this huge error. I'm sorry, to all the mailing list, for such an useless post... Anyway, thanks again Dan, have a nice day guys 2017-07-26 13

Re: [sqlite] Foreign key mismatch with ON DELETE clauses

On 07/25/2017 08:42 PM, Bubu Bubu wrote: I'm having trouble with foreign key constraints et cascade clauses and I don't really know where could be the problem. I'm working on sqlite version 3.8.11.1 (I can't update it, work restriction), but I checked and such functionnalities are enabled (correc

[sqlite] Foreign key mismatch with ON DELETE clauses

I'm having trouble with foreign key constraints et cascade clauses and I don't really know where could be the problem. I'm working on sqlite version 3.8.11.1 (I can't update it, work restriction), but I checked and such functionnalities are enabled (correct me if I'm wrong). I've renamed everythin

Re: [sqlite] foreign key constraint failure

Thanks for the clever ideas. In my case I figured it out by hand (it was a trigger which was inserting a row with a foreign key into another table that no longer existed). But I will make use of those strategies in the future. On Tue, May 9, 2017 at 11:54 PM, Gwendal Roué wrote: > There is a

Re: [sqlite] foreign key constraint failure

There is a way, but it requires some effort: First let's define a schema that reproduces your error: CREATE TABLE t1 ( id INTEGER PRIMARY KEY); CREATE TABLE t2 ( id INTEGER PRIMARY KEY, id1 INTEGER REFERENCES t1(id) ON DELETE RESTRICT); INSERT INTO t1 (id) VALUES (12

Re: [sqlite] foreign key constraint failure

On Wed May 10, 2017 at 08:34:42AM +0200, Clemens Ladisch wrote: > Mark Wagner wrote: > > Is there a way to get sqlite to tell which foreign key constraint is > > causing a failure? > > No; to make the implementation of deferred constraints easier, it keeps > track only of the number of remaining f

Re: [sqlite] foreign key constraint failure

Mark Wagner wrote: > Is there a way to get sqlite to tell which foreign key constraint is > causing a failure? No; to make the implementation of deferred constraints easier, it keeps track only of the number of remaining foreign key failures, not of their origin. Regards, Clemens ___

[sqlite] foreign key constraint failure

Is there a way to get sqlite to tell which foreign key constraint is causing a failure? Some kind of verbose mode? Thanks! sqlite> delete from t; Error: FOREIGN KEY constraint failed sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqli

[sqlite] foreign key referencing an undefined table?

Dear, I just stumbled on this: looks like I can create a table with a foreign key referencing an unknown table. sqlite> pragma foreign_keys; 1 sqlite> create table T(C integer, foreign key(C) references X(Y)); sqlite> pragma integrity_check; ok sqlite> pragma foreign_key_check; Is there some me

Re: [sqlite] foreign key cardinality

On Wednesday, 1 March, 2017 12:21, James K. Lowden wrote: > Probably I'm overlooking something, and probably this is not the right > forum. SQLite adheres to the SQL standard; that's established. Why > does the standard say what it does? I'm only saying it's not clear to > me that there's the

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 18:01:35 -0800 J Decker wrote: > 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. That's an interesting way to put it. One of Codd's first

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 19:44:56 -0700 "Keith Medcalf" wrote: > It has always been a requirement that FK relationships are 1:N > mappings, otherwise update anomalies will occur. If you have a > relational database that does not "naturally" have all FK > relationships as 1:N, then you have not normal

Re: [sqlite] foreign key cardinality

On Wed, 1 Mar 2017 07:26:31 + Hick Gunter wrote: > Without a UNIQUE constraint there can be more than one row in the > parent table that is the "parent row" of at least one row of the > child table. To delete rows from the parent while still satisfying > the EXISTS relation, you can delete al

Re: [sqlite] foreign key cardinality

sprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Dienstag, 28. Februar 2017 22:41 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] foreign key cardinality On Tue, 28 Feb 2017 17:13:30 + Hick

Re: [sqlite] foreign key cardinality

> > 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

Re: [sqlite] foreign key cardinality

On Tue, Feb 28, 2017 at 6:01 PM, J Decker wrote: > > > On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden > wrote: > >> On Tue, 28 Feb 2017 17:13:30 + >> Hick Gunter wrote: >> >> > Let's assume you have a parent table P (color, shape, style, ...) >> > that is unique on the three named fields.

Re: [sqlite] foreign key cardinality

On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden wrote: > On Tue, 28 Feb 2017 17:13:30 + > Hick Gunter 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,

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 17:13:30 + Hick Gunter 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 pro

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 11:42:23 -0500, "James K. Lowden" wrote: > I have always thought af a foreign key as an existence test. Looking > around, I see that other implementations also often require the > referent to be unique. I'm not convinced that's justified > theoretically, but at the moment I

Re: [sqlite] foreign key cardinality

lto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Dienstag, 28. Februar 2017 17:42 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] foreign key cardinality On Tue, 28 Feb 2017 08:48:02 + Hick Gunter wrote: > "If they are not the pri

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 08:48:02 + Hick Gunter wrote: > "If they are not the primary key, then the parent key columns must > be collectively subject to a UNIQUE constraint or have a UNIQUE > index." Thank you, Hick. I have always thought af a foreign key as an existence test. Looking around,

Re: [sqlite] foreign key cardinality

] foreign key cardinality On 28 Feb 2017, at 12:19am, James K. Lowden wrote: > sqlite> create table A(a, b, primary key (a,b)); create table C(c > sqlite> references A(a)); The reference column(s) (the column(s) in the 'parent' table) must be UNIQUE otherwise you may have

Re: [sqlite] foreign key cardinality

e Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Montag, 27. Februar 2017 21:42 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] foreign key cardinality I would like to illustrate a problem with SQLite's foreign key enforce

Re: [sqlite] foreign key cardinality

On 28 Feb 2017, at 12:19am, James K. Lowden wrote: > sqlite> create table A(a, b, primary key (a,b)); > sqlite> create table C(c references A(a)); The reference column(s) (the column(s) in the 'parent' table) must be UNIQUE otherwise you may have two rows in that table which both look like the

Re: [sqlite] foreign key cardinality

On Mon, 27 Feb 2017 16:07:48 -0500 Richard Hipp wrote: > On 2/27/17, James K. Lowden wrote: > > SQLite requires that foreign keys refer to primary > > keys. > > No it doesn't. Where did you get that impression? sqlite> create table A(a, b, primary key (a,b)); sqlite> create table C(c referenc

Re: [sqlite] foreign key cardinality

The column can be unique as well, correct? SQLite version 3.17.0 2017-02-13 16:02:40 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> pragma foreign_keys = 1; sqlite> create table x (a integer primary key,

Re: [sqlite] foreign key cardinality

On 2/27/17, James K. Lowden wrote: > SQLite requires that foreign keys refer to primary > keys. No it doesn't. Where did you get that impression? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org ht

[sqlite] foreign key cardinality

I would like to illustrate a problem with SQLite's foreign key enforcement policy. SQLite requires that foreign keys refer to primary keys. That rule has no relational foundation, and prevents the use of foreign keys that are perfectly valid. I have these tables (non-key columns omitted for brev

Re: [sqlite] FOREIGN KEY question

On Wed, Feb 8, 2017 at 12:24 AM, Clemens Ladisch wrote: > Igor Korot wrote: > > Does SQLite supports the FK name? > > If yes, what is the proper syntax? > > CREATE TABLE t ( > x PRIMARY KEY, > y, > CONSTRAINT this_is_the_name FOREIGN KEY (y) REFERENCES t(x) > ); > > or > > CREAT

Re: [sqlite] FOREIGN KEY question

Igor Korot wrote: > Does SQLite supports the FK name? > If yes, what is the proper syntax? CREATE TABLE t ( x PRIMARY KEY, y, CONSTRAINT this_is_the_name FOREIGN KEY (y) REFERENCES t(x) ); or CREATE TABLE t ( x PRIMARY KEY, y CONSTRAINT this_is_the_name REFERENCES t(x)

Re: [sqlite] FOREIGN KEY question

On 8 Feb 2017, at 5:03am, Igor Korot wrote: > Does SQLite supports the FK name? > If yes, what is the proper syntax? Foreign keys do not have names in SQLite. You must define the FK as part of the child table definition. You cannot add it after. Si

Re: [sqlite] FOREIGN KEY question

Database ; General Discussion of SQLite Database Betreff: [sqlite] FOREIGN KEY question Hi, ALL, Does SQLite supports the FK name? If yes, what is the proper syntax? Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

[sqlite] FOREIGN KEY question

Hi, ALL, Does SQLite supports the FK name? If yes, what is the proper syntax? Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

Keith, Good point. Did not know this exists. Ken On 01/10/2017 09:48 PM, Simon Slavin wrote: On 11 Jan 2017, at 1:02am, Keith Medcalf wrote: You are correct, however, if there were a unique constraint placed on tracks.name, then a given track could only appear once (in the first case), or

Re: [sqlite] Foreign key error...

On 11 Jan 2017, at 1:02am, Keith Medcalf wrote: > You are correct, however, if there were a unique constraint placed on > tracks.name, then a given track could only appear once (in the first case), > or in multiple places (in the second case). _The Power of Love_ was recorded by Frankie Goes

Re: [sqlite] Foreign key error...

te.org] > On Behalf Of Ken Wagner > Sent: Monday, 9 January, 2017 23:46 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Keith, > > "this does not allow the same track on multiple albums" with the same > trackno, but a different tr

Re: [sqlite] Foreign key error...

ctually signifys, so yeah, it's a little hidden. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden Sent: Tuesday, January 10, 2017 1:14 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Foreign key

Re: [sqlite] Foreign key error...

On Sun, 08 Jan 2017 05:57:46 -0700 "Keith Medcalf" wrote: > artistid integer references artists Hmph. Learn something new every day. Where is that abbreviated form documented? I looked for "references" on the Create Table page, and didn't find anything about its default arguments. --jkl

Re: [sqlite] Foreign key error...

ed.TheCommonColumnNameBetweenTheFirstAndTheSecondTabl e = TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTab le; -Original Message- From: sqlite-users [mailto:sqlite-users- boun...@mailinglists.sqlite.org] On Behalf Of Ken Wagner Sent: Sunday, 8 January, 2017 04:04 To: SQLite mailing list Subject: Re: [s

Re: [sqlite] Foreign key error...

Yes, thanks. The 'left join on' or 'inner join on ' removes the chance of an erroneous key linkage. Also makes sense to pay close attention as to which table is left and right. Ken On 01/09/2017 06:46 AM, Dominique Devienne wrote: On Sun, Jan 8, 2017 at 12:46 PM, Keith Medcalf wrote:

Re: [sqlite] Foreign key error...

On Sun, Jan 8, 2017 at 12:46 PM, Keith Medcalf wrote: > > ... join ... using (column) has nothing whatever to do with foreign keys. > > "FROM a JOIN b USING (c) is "syntactic sugar" ([...]) for the expression > "FROM a, b WHERE a.c = b.c" > Or "FROM a JOIN b ON a.c = b.c". Or "FROM a INNER JOIN

Re: [sqlite] Foreign key error...

USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable); > > > -vs- > > > SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE > > TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheF > irstAndTheSecondTabl > > e = > > TheSecondTableToBeJoined.TheCommonCo

Re: [sqlite] Foreign key error...

t; > > > > >> -Original Message- > >> From: sqlite-users [mailto:sqlite-users- > boun...@mailinglists.sqlite.org] > >> On Behalf Of Ken Wagner > >> Sent: Sunday, 8 January, 2017 04:04 > >> To: SQLite mailing list > >> Subj

Re: [sqlite] Foreign key error...

monColumnNameBetweenTheFirstAndTheSecondTable = TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ken Wagner Sent: Sunday, 8 January, 2017 04:04 To: SQLite mailing

Re: [sqlite] Foreign key error...

t: Sunday, 8 January, 2017 04:04 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Thanks, Kees, > > The message is helpful as a warning. > > select artistname, trackname from artist inner join track on > trackartist = artistid; works jus

Re: [sqlite] Foreign key error...

Thanks, Kees, The message is helpful as a warning. select artistname, trackname from artist inner join track on trackartist = artistid; works just fine. But isn't the efficiency of 'using (artistid)' more desirable? Is the use of a 'trackerartist' as the foreign key used because it is more i

Re: [sqlite] Foreign key error...

On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner wrote: >Hello SQLusers, > > The error below occurs even though the > > CREATE TABLE track( > > trackid INTEGER, > trackname TEXT, > trackartist INTEGER, > *FOREIGN KEY(trackartist) REFERENCES artist(artistid)* > ); > > statement at https

[sqlite] Foreign key error...

Hello SQLusers, The error below occurs even though the CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, *FOREIGN KEY(trackartist) REFERENCES artist(artistid)* ); statement at https://sqlite.org/foreignkeys.html was observed. It appears that 'trackerartis

Re: [sqlite] Foreign key integrity checking.

On Fri, Jan 6, 2017 at 10:30 PM, David Raymond wrote: > http://www.sqlite.org/foreignkeys.html > > My version of the very basic basics anyway: > -Foreign key enforcement is turned on or off with a pragma and is a > connection-level setting, not a property of the database itself. > -The default en

Re: [sqlite] Foreign key integrity checking.

g] On Behalf Of Michael Tiernan Sent: Friday, January 06, 2017 4:07 PM To: SQLite mailing list Subject: [sqlite] Foreign key integrity checking. I'm going to assume this has come up before so instead of asking for help, I'll simply ask for pointers to FAQs about some of the more mundane thi

[sqlite] Foreign key integrity checking.

I'm going to assume this has come up before so instead of asking for help, I'll simply ask for pointers to FAQs about some of the more mundane things such as ensuring foreign key integrity and checking for it. Thanks for everyone's time. -- << MCT >> Michael C Tiernan. http://www.linkedin.co

Re: [sqlite] Foreign key child index question

g] 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 wrote: > sqlite> create table parentTable >...> ( >...>

Re: [sqlite] Foreign key child index question

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> create table childTable >

Re: [sqlite] Foreign key child index question

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 wrote: > >> On 13 Dec 2016, at 3:53pm, Pa

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 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 means that it does n

Re: [sqlite] Foreign key child index question

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 chosen to do for your case

Re: [sqlite] Foreign key child index question

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 > > means that it does not have a parent. > > You

Re: [sqlite] Foreign key child index question

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 original poster was correct,

Re: [sqlite] Foreign key child index question

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 needs to know how to find the parent row for tha

Re: [sqlite] Foreign key child index question

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 field(s) are defined as NOT

Re: [sqlite] Foreign key child index question

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 majority of > the time are null. I've currently g

Re: [sqlite] Foreign key child index question

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 for the > purposes of spee

[sqlite] Foreign key child index question

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 up

[sqlite] FOREIGN KEY constraint failed

On Tue, 5 Apr 2016 23:56:53 +0200 R Smith wrote: > On 2016/04/05 11:15 PM, Keith Medcalf wrote: > > Are we confusing immediate constraints (checked per statement) with > > DEFERRED constraints (checked at COMMIT time) again? In SQLite some constraints are checked per row, not per statement. >

[sqlite] FOREIGN KEY constraint failed

On Tue Apr 05, 2016 at 11:56:53PM +0200, R Smith wrote: > > On 2016/04/05 11:15 PM, Keith Medcalf wrote: > >Are we confusing immediate constraints (checked per statement) with > >DEFERRED constraints (checked at COMMIT time) again? > > > > We might be - though I assume the OP implicated only defe

[sqlite] FOREIGN KEY constraint failed

On 2016/04/05 11:15 PM, Keith Medcalf wrote: > Are we confusing immediate constraints (checked per statement) with DEFERRED > constraints (checked at COMMIT time) again? > We might be - though I assume the OP implicated only deferred constraints - since immediate constraints will fail on conta

[sqlite] FOREIGN KEY constraint failed

th > Sent: Tuesday, 5 April, 2016 06:58 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] FOREIGN KEY constraint failed > > > > On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote: > > Thanks for reply ! > > > > I already sent a propos

[sqlite] FOREIGN KEY constraint failed

veryday problems, (I tried to find a way to do it myself through the sqlite3 sources but I couldn't find my way through the virtual machine with the available documentation). Cheers ! > Tue Apr 05 2016 12:58:07 PM CEST from "R Smith" >Subject: Re: [sqlite] FOREIGN KEY c

[sqlite] FOREIGN KEY constraint failed

On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote: > Thanks for reply ! > > I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" and > when set throw any kind of error to stderr/sqlite3_(hook) this way ther is no > need to store temporary conditions to show later. > > And o

[sqlite] FOREIGN KEY constraint failed

On Mon, 04 Apr 2016 17:51:30 +0200 Domingo Alvarez Duarte wrote: >FOREIGN KEY constraint failed >I have some databases with lots of foreign keys and I sometimes I need to >change the structure of some tables and I get this message Before restructuring a database schema always use: PRAGMA fore

[sqlite] FOREIGN KEY constraint failed

o flag the error, it doesn't throw a dice to do it. Cheers ! > Mon Apr 04 2016 10:35:35 PM CEST from "Petite Abeille" > Subject: Re: [sqlite] FOREIGN KEY constraint >failed > > >>On Apr 4, 2016, at 6:14 PM, Richard Hipp wrote: >> >> On 4/4/16,

[sqlite] FOREIGN KEY constraint failed

. Cheers ! > Mon Apr 04 2016 10:17:00 PM CEST from "Tim Streater" > Subject: Re: [sqlite] FOREIGN KEY constraint failed > > On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte > wrote: > > >>Fair enough ! >> >> But even then could it have le

[sqlite] FOREIGN KEY constraint failed

> On Apr 4, 2016, at 6:14 PM, Richard Hipp wrote: > > On 4/4/16, Domingo Alvarez Duarte wrote: >> sqlite knows which table/field failed > > No it doesn't, actually. And yet, that same question comes over, and over, and over, ad nauseam. Each and every time a poor soul is confronted with tha

[sqlite] FOREIGN KEY constraint failed

On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte wrote: > Fair enough ! > > But even then could it have let's say a place for record the last foreign key > violation ? > > Only one place will be better than nothing, it can be overwritten every time > a foreign key is found and at least we could

[sqlite] FOREIGN KEY constraint failed

Thanks for reply ! Thank you for your solution I think it improves the actual situation ! Cheers ! > Mon Apr 04 2016 08:00:38 PM CEST from "Stephen Chrzanowski" > Subject: Re: [sqlite] FOREIGN KEY constraint failed > > I've never developed for a platform wi

[sqlite] FOREIGN KEY constraint failed

can be run without problem. ? > Mon Apr 04 2016 07:49:57 PM CEST from "Domingo Alvarez Duarte" > Subject: Re: [sqlite] FOREIGN KEY constraint >failed > > Thank you for reply ! > > Good point, but for sure there is great minds here that can come with a > solutio

[sqlite] FOREIGN KEY constraint failed

be done lazy so if no violations occur no hash table is ever created. The actual situation is better than nothing but it's still frustrating ! Cheers ! > Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch" > Subject: Re: [sqlite] FOREIGN KEY constraint failed

  1   2   3   >