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

[Keith Medcalf]

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 index and using both may result in extraneous 
duplicate indexes.  Get rid of UNIQUE.

>CREATE TABLE lookups (
> lookup_id  INTEGER  PRIMARY KEY AUTOINCREMENT,
> content_hash   TEXT COLLATE NOCASE,
> error_code INTEGER  REFERENCES error_codes (error_code),
> exp_content_type   TEXT COLLATE NOCASE
>);
>CREATE INDEX idx__content_hash ON lookups (
> content_hash
>);

You also need an index on "error_code" unless you want to "wait awhile" while 
some enforcement operations perform a table scan to find children rather than a 
simple index lookup ...

>So I try and do an update on my data:
> UPDATE lookups set error_code=3 and
>exp_content_type='ogc_except'
>WHERE content_hash = '0027f2c9b80002a6';

Clemens already explained that this is probably not what you wanted to do.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jonathan Moules]

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 because "3 and exp_content_type='ogc_except'" is interpreted as
a boolean expression.

To update multiple fields, separate them with commas:

   UPDATE lookups set error_code=3, exp_content_type='ogc_except' WHERE ...
  ^


UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

This is not valid SQL ("and WHERE").


Regards,
Clemens
___
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

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

[Clemens Ladisch]

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 lookups set error_code=3, exp_content_type='ogc_except' WHERE ...
 ^

> UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

This is not valid SQL ("and WHERE").


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint fails due to unrelated field

[Jonathan Moules]

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 done using "ON UPDATE CASCADE," on the error_code field in 
lookups table - this was temporary and has since been removed. I then 
changed the error codes in the error table and SQLite automagically 
updated the lookups table. Brilliant.

But today I'm seeing oddness.

I start by running:
    pragma foreign_key_check;
    pragma integrity_check;

Both return fine.

The below sample data replicates it for me.
My error table schema (with a subset of data) is:

CREATE TABLE error_codes (
    error_code INTEGER PRIMARY KEY
   UNIQUE,
    error  TEXT
);

INSERT INTO error_codes (error_code, error) VALUES (0, 'No Error');
INSERT INTO error_codes (error_code, error) VALUES (3, 'badness');
INSERT INTO error_codes (error_code, error) VALUES (60, 'more badness');

etc.

Referencing this I have a table called lookups (shortened here):

CREATE TABLE lookups (
    lookup_id  INTEGER  PRIMARY KEY AUTOINCREMENT,
    content_hash   TEXT COLLATE NOCASE,
    error_code INTEGER  REFERENCES error_codes (error_code),
    exp_content_type   TEXT COLLATE NOCASE
);
CREATE INDEX idx__content_hash ON lookups (
    content_hash
);

INSERT INTO lookups (lookup_id, content_hash, error_code, 
exp_content_type) VALUES (661228, '0027f2c9b80002a6', 0, 'ogc_except');


(there are 4 million rows in the actual data)

So I try and do an update on my data:
    UPDATE lookups set error_code=3 and exp_content_type='ogc_except' 
WHERE content_hash = '0027f2c9b80002a6';


and it fails with:
"FOREIGN KEY constraint failed"

So here's where it gets weird, the following work:
-- error_code of 0
UPDATE lookups set error_code=0 and exp_content_type='ogc_except' WHERE 
content_hash = '0027f2c9b80002a6';


-- setting the exp_content_type to something else
UPDATE lookups set error_code=3 and exp_content_type='SOMETHING_ELSE' 
WHERE content_hash = '0027f2c9b80002a6';


-- Removing the exp_content_type field:
UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

For bonus oddness, the update script is actually meant to be running in 
Python, which did run it successfully (even though I'm sure FK 
constraints remain enabled), and yet when I select the row, it shows an 
error_code of 1! Despite the fact the UPDATE SQL being run is very 
definitely "set error_code = 60".

Note that error_code 1 USED to be what is now error_code 60!


TL;DR:
* For some reason the insertion value of an unrelated row seems to be 
affecting referential integrity.
* For some reason when updating to error_code = 60 via Python (and I've 
confirmed the SQL being run does have this) it actually gets updated to 
error_code = 1 (what the code used to be).


Any thoughts what's going on here? I think either SQLite has its wires 
crossed or maybe I'm missing something (probably simple).


SQLite 3.24.0

Thanks,
Jonathan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Kees Nuyt]

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 records that do not fulfill
> the constraint, i.e. having values in the foreign key column
> that are not present in the primary key column of the first
> table. When I insert or edit a single record using SQLCipher's
> DB Browser for SQLite, I am correctly being rejected. I can
> insert a record with NULL (so I need a NOT NULL on the foreign
> key column, actually), but not with a value that do not exist
> in the first table.
> 
> I can find nothing in the documentation about foreign key
> constraints not being respected for bulk inserts like this,
> but perhaps there is such a rule anyway? I found an old bug
> report, 29ab7be99f2cfe0b04466dd95b80ace87e9fc1b2, "Hitting NOT
> NULL constraint does not roll back statement transaction",
> that resembles it a bit on the title at least, but it is not
> the same.
> 
> Are constraints disabled for bulk inserts? It does execute
> very fast, so... - or have I found a bug? - or am doing
> something wrong?

Did you enable foreign key checking with
PRAGMA foreign_keys=on;
? It is a per-connection setting, not retained in the database.
It is off by default. Indeed bulk inserts are fast without it.

https://sqlite.org/pragma.html#pragma_foreign_keys
https://sqlite.org/pragma.html#pragma_foreign_key_check

-- 
Regards,

Kees Nuyt


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Morten Joergensen]

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 key column that 
are not present in the primary key column of the first table. When I insert or 
edit a single record using SQLCipher's DB Browser for SQLite, I am correctly 
being rejected. I can insert a record with NULL (so I need a NOT NULL on the 
foreign key column, actually), but not with a value that do not exist in the 
first table.

I can find nothing in the documentation about foreign key constraints not being 
respected for bulk inserts like this, but perhaps there is such a rule anyway? 
I found an old bug report, 29ab7be99f2cfe0b04466dd95b80ace87e9fc1b2, "Hitting 
NOT NULL constraint does not roll back statement transaction", that resembles 
it a bit on the title at least, but it is not the same.

Are constraints disabled for bulk inserts? It does execute very fast, so... - 
or have I found a bug? - or am doing something wrong?

Morten Due Jørgensen  |  Schneider Electric  |  Automation  |  Sr. Software 
Development Engineer
Phone: +45 88302000 |  Direct Phone: +45 88302653
Email: morten.joergen...@schneider-electric.com  |  Site: 
www.schneider-electric.com  |  Address: Lautrupvang 1, DK-2750 Ballerup, Denmark

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key error

[J Decker]

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.



On Mon, Jul 30, 2018 at 11:13 PM Clemens Ladisch  wrote:

> 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 primary key, or at least have a unique
> constraint/index.
>
>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] Foreign Key error

[Clemens Ladisch]

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 primary key, or at least have a unique
constraint/index.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key error

[J Decker]

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','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f');
Error: foreign key mismatch - "option4_map" referencing "option4_name"
sqlite> select * from option4_name where name like 'system settings';
cc47f95a-e79a-11e7-872c-2e6fc90d301f|System Settings

I have kinda an old command line tool - but it does the same thing.

On Mon, Jul 30, 2018 at 8:11 PM J Decker  wrote:

>
>
> 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 different results when using different options?  (of course,
>> if you turn off foreign key enforcement then you will not get errors when
>> you violate declared foreign key constraints), so other than that obviously?
>>
>> It seems to be complaining about a foreign-key reference not existing.
>> The only option which would affect that is having foreign-keys turned on.
>> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
>> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
>> enforced -- the default state is ON rather than OFF)
>>
>> no, removing all options (except the one for metadata) and enabling
> pragma foreign_keys=on the error still happens (I had enabled it on the
> reader but not the writer, on the writer I added the keys=on and
> integrity_check ) and
>
> PRAGMA foreign_key_check
> PRAGMA foreign_key_check(option4_map)
>
> There doesn't appear to be existing errors.
>
> ```
> 
>
> pragma integrity_check
> pragma foreign_keys=on
> select * from option4_name where name = 'system Settings'
> no data
> PRAGMA foreign_key_check
> PRAGMA foreign_key_check(option4_map)
> PRAGMA foreign_key_list(option4_map)
> 0,0,option4_name,name_id,name_id,CASCADE,CASCADE,NONE
> 1,0,option4_map,parent_option_id,option_id,CASCADE,CASCADE,NONE
>
> Do Command[01888DA2FC6C:*/../option.db]: select * from sqlite_master
>  CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)
>  index,sqlite_autoindex_option4_name_1,option4_name,3,
>  table,option4_map,option4_map,4,CREATE TABLE `option4_map` (`option_id`
> char(36) NOT NULL,`parent_option_id` char(36) NOT NULL default
> '0',`name_id` char(36) NOT NULL default '0',`description`
> tinytext,CONSTRAINT `parent_key2` UNIQUE (`parent_option_id`,`name_id`) ON
> CONFLICT REPLACE, FOREIGN KEY  (`parent_option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY
> (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE
> CASCADE)
>  index,sqlite_autoindex_option4_map_1,option4_map,5,
>  CREATE TABLE `option4_values` (`option_id` char(36) default '0',`string`
> varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
> UNIQUE (`option_id`,`segment`) ON CONFLICT REPLACE, FOREIGN KEY
> (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
> DELETE CASCADE)
>  index,sqlite_autoindex_option4_values_1,option4_values,7,
>  CREATE TABLE `option4_blobs` (`option_id` char(36) default '0' CONSTRAINT
> `value_id` UNIQUE,`binary` blob, FOREIGN KEY  (`option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
>  index,sqlite_autoindex_option4_blobs_1,option4_blobs,9,
>  CREATE TABLE `option4_exception` (`option_exception_id` char(36) NOT NULL
> CONSTRAINT `option_exception_idkey` UNIQUE,`apply_from` datetime default
> '-00-00 00:00:00',`apply_until` datetime default '-00-00
> 00:00:00',`system_id` int(11) NOT NULL default '0',`override_value_id`
> char(36) NOT NULL default '0',`option_id` char(36) NOT NULL default
> '0',CONSTRAINT `FK_map_exception` FOREIGN KEY (`option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
>  index,sqlite_autoindex_option4_exception_1,option4_exception,61,
>
>
> BEGIN TRANSACTION
> Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values
> ('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')
> Error inserting option: Result of prepare failed? foreign key mismatch -
> "option4_map" referencing "option4_name" at char 191[] in [Insert into
> option4_map(`option_id`,`parent_option_id`,`name_id`) values
> ('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')]
>
> 
> ```
>
>
>> To understand what foreign-key constraint you are violating one would
>> need to see the

Re: [sqlite] Foreign Key error

[J Decker]

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 different results when using different options?  (of course, if
> you turn off foreign key enforcement then you will not get errors when you
> violate declared foreign key constraints), so other than that obviously?
>
> It seems to be complaining about a foreign-key reference not existing.
> The only option which would affect that is having foreign-keys turned on.
> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
> enforced -- the default state is ON rather than OFF)
>
> no, removing all options (except the one for metadata) and enabling pragma
foreign_keys=on the error still happens (I had enabled it on the reader but
not the writer, on the writer I added the keys=on and integrity_check ) and

PRAGMA foreign_key_check
PRAGMA foreign_key_check(option4_map)

There doesn't appear to be existing errors.

```


pragma integrity_check
pragma foreign_keys=on
select * from option4_name where name = 'system Settings'
no data
PRAGMA foreign_key_check
PRAGMA foreign_key_check(option4_map)
PRAGMA foreign_key_list(option4_map)
0,0,option4_name,name_id,name_id,CASCADE,CASCADE,NONE
1,0,option4_map,parent_option_id,option_id,CASCADE,CASCADE,NONE

Do Command[01888DA2FC6C:*/../option.db]: select * from sqlite_master
 CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)
 index,sqlite_autoindex_option4_name_1,option4_name,3,
 table,option4_map,option4_map,4,CREATE TABLE `option4_map` (`option_id`
char(36) NOT NULL,`parent_option_id` char(36) NOT NULL default
'0',`name_id` char(36) NOT NULL default '0',`description`
tinytext,CONSTRAINT `parent_key2` UNIQUE (`parent_option_id`,`name_id`) ON
CONFLICT REPLACE, FOREIGN KEY  (`parent_option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY
(`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE
CASCADE)
 index,sqlite_autoindex_option4_map_1,option4_map,5,
 CREATE TABLE `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`) ON CONFLICT REPLACE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
 index,sqlite_autoindex_option4_values_1,option4_values,7,
 CREATE TABLE `option4_blobs` (`option_id` char(36) default '0' CONSTRAINT
`value_id` UNIQUE,`binary` blob, FOREIGN KEY  (`option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
 index,sqlite_autoindex_option4_blobs_1,option4_blobs,9,
 CREATE TABLE `option4_exception` (`option_exception_id` char(36) NOT NULL
CONSTRAINT `option_exception_idkey` UNIQUE,`apply_from` datetime default
'-00-00 00:00:00',`apply_until` datetime default '-00-00
00:00:00',`system_id` int(11) NOT NULL default '0',`override_value_id`
char(36) NOT NULL default '0',`option_id` char(36) NOT NULL default
'0',CONSTRAINT `FK_map_exception` FOREIGN KEY (`option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
 index,sqlite_autoindex_option4_exception_1,option4_exception,61,


BEGIN TRANSACTION
Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values
('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')
Error inserting option: Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 191[] in [Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')]


```


> To understand what foreign-key constraint you are violating one would need
> to see the database schema -- however my initial guess would be that that a
> option4_name is being checked when you provided an name_id -- that you have
> your constraints crossed (or collation for text fields set incorrectly,
> perhaps)
>
> > ... select name_id from
> >option4_name
> >where name like 'System Settings'
>
> Why are you using "like" instead of "==" ... there is no wildcard in the
> string ...
>

turns out it's for case insensitivity...
select * from option4_name where name like 'system Settings'
cc47f95a-e79a-11e7-872c-2e6fc90d301f,System Settings

but
select * from option4_name where name = 'system Settings'
-no data-






> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users maili

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

[David Raymond]

Basic question, did you remember to turn on foreign key support? The default 
default is for foreign key support to be off when you connect. So unless you 
ran "pragma foreign_keys = on;" or compiled with SQLITE_DEFAULT_FOREIGN_KEYS=1 
then it won't enforce them.


-Original Message-
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 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:


#!/bin/sh

rm -f /tmp/sqlite3.db

sqlite3 -version
# 3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

cat <<"EOF" | sqlite3 -batch -echo /tmp/sqlite3.db
/* Example from https://sqlite.org/foreignkeys.html */
CREATE TABLE artist(
  artistidINTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
-- This fails because the value inserted into the trackartist column (3)
-- does not correspond to row in the artist table.
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
EOF

sqlite3 /tmp/sqlite3.db 'SELECT * FROM track'
# Should be empty as the INSERT should have failed
# However, the result is:
#14|Mr. Bojangles|3





Gruß,
___

Tobias Kühne
Produktsoftwareentwickler
JOSEPH VÖGELE AG
Joseph-Vögele-Str. 1, D-67075 Ludwigshafen
www.voegele.info

T:  +49 621 / 8105 431
F:  +49 621 / 8105 493
tobias.kue...@voegele.info
___

Joseph Vögele Aktiengesellschaft, Ludwigshafen
Amtsgericht Ludwigshafen HRB 62108
Vorsitzender des Aufsichtsrats: Domenic G. Ruccolo   Vorstand: Dipl.-Ing. 
Bernhard Düser   Dr.-Ing. Christian Pawlik

___
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] FOREIGN KEY allows INSERT where it should not

[Kühne , Tobias]

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:


#!/bin/sh

rm -f /tmp/sqlite3.db

sqlite3 -version
# 3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

cat <<"EOF" | sqlite3 -batch -echo /tmp/sqlite3.db
/* Example from https://sqlite.org/foreignkeys.html */
CREATE TABLE artist(
  artistidINTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
-- This fails because the value inserted into the trackartist column (3)
-- does not correspond to row in the artist table.
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
EOF

sqlite3 /tmp/sqlite3.db 'SELECT * FROM track'
# Should be empty as the INSERT should have failed
# However, the result is:
#14|Mr. Bojangles|3





Gruß,
___

Tobias Kühne
Produktsoftwareentwickler
JOSEPH VÖGELE AG
Joseph-Vögele-Str. 1, D-67075 Ludwigshafen
www.voegele.info

T:  +49 621 / 8105 431
F:  +49 621 / 8105 493
tobias.kue...@voegele.info
___

Joseph Vögele Aktiengesellschaft, Ludwigshafen
Amtsgericht Ludwigshafen HRB 62108
Vorsitzender des Aufsichtsrats: Domenic G. Ruccolo   Vorstand: Dipl.-Ing. 
Bernhard Düser   Dr.-Ing. Christian Pawlik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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 when 
someone's bored.


sqlite> create table parent (id integer primary key);

sqlite> create table child1 (id integer primary key, parentID int references 
parent);

sqlite> .lint fkey-indexes
CREATE INDEX 'child1_parentID' ON 'child1'('parentID'); --> parent(id)

sqlite> create index idx_child1 on child1 (parentID);

sqlite> .lint fkey-indexes

sqlite> create table child2 (id int primary key, parentID int references 
parent) without rowid;

sqlite> .lint fkey-indexes
no such column: rowid

sqlite> create index idx_child2 on child2 (parentID);

sqlite> .lint fkey-indexes
no such column: rowid

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

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

[David Raymond]

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: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 when 
the command line tool starts up.

Simon.
___
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

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

[Simon Slavin]

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 when 
the command line tool starts up.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key lint has issues with without rowid

[David Raymond]

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 references 
parent);

sqlite> .lint fkey-indexes
CREATE INDEX 'child1_parentID' ON 'child1'('parentID'); --> parent(id)

sqlite> create index idx_child1 on child1 (parentID);

sqlite> .lint fkey-indexes

sqlite> create table child2 (id int primary key, parentID int references 
parent) without rowid;

sqlite> .lint fkey-indexes
no such column: rowid

sqlite> create index idx_child2 on child2 (parentID);

sqlite> .lint fkey-indexes
no such column: rowid

sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key help

[x]

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 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 referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>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 enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
><https://sqlite.org/pragma.html#pragma_foreign_key_check>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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

Re: [sqlite] Foreign key help

[Keith Medcalf]

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 referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>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 enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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

Re: [sqlite] Foreign key help

[x]

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

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using

<https://sqlite.org/pragma.html#pragma_foreign_key_check>

PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

Simon.
___
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

Re: [sqlite] Foreign key help

[David Raymond]

Leaves what's there alone and just starts enforcing from when you turn it on. 
It'll only go actively looking for issues if you do a pragma foreign_key_check;

sqlite> pragma foreign_keys = off;

sqlite> create table parent (id integer primary key);

sqlite> create table child (id integer primary key, parentID int references 
parent);

sqlite> insert into parent values (1), (2);

sqlite> insert into child (parentID) values (1), (2), (3), (4);

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|3|parent|0
child|4|parent|0

sqlite> pragma foreign_keys = on;

sqlite> insert into parent values (3);

sqlite> update child set parentID = parentID + 1;
Error: FOREIGN KEY constraint failed

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|4|parent|0


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@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 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?

___
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

Re: [sqlite] Foreign key help

[Simon Slavin]

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 enforce foreign keys from that point on?

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using



PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key help

[x]

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?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 about it.  We’ve all done it.  Glad you figured it out.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Bubu Bubu]

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:04 GMT+02:00 Dan Kennedy :

> 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 (correct
>> me if I'm wrong).
>>
>> I've renamed everything, hope this will be readable :
>>
>> PRAGMA foreign_keys = true;
>>
>> CREATE TABLE T_A (
>> id_t_a INTEGER PRIMARY KEY,
>> label TEXT
>> );
>>
>> CREATE TABLE T_B (
>> id_t_b INTEGER PRIMARY KEY,
>> fk_t_a_id INTEGER NOT NULL,
>> label TEXT,
>> FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
>> CASCADE
>> );
>>
>> CREATE TABLE T_C (
>> id_t_c INTEGER PRIMARY KEY,
>> fk_t_a_id INTEGER NOT NULL,
>> fk_t_b_id INTEGER,
>> FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
>> CASCADE,
>> FOREIGN_KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
>> CASCADE
>> );
>>
>> CREATE TABLE T_D (
>> id_t_d INTEGER PRIMARY KEY,
>> fk_t_c_id INTEGER NOT NULL,
>> fk_t_b_id INTEGER,
>> FOREIGN KEY(fk_t_c_id) REFERENCES T_C(id_t_c) ON DELETE CASCADE ON UPDATE
>> CASCADE,
>> FOREIGN KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
>> CASCADE
>> );
>>
>> And then I'm doing this :
>>
>> INSERT INTO T_A(label) VALUES("A1");   // id_t_a -> 1
>> INSERT INTO T_B(label, fk_t_a_id) VALUES("B1", 1);
>> INSERT INTO T_B(label, fk_t_a_id) VALUES("B2", 1);
>>
>> At this point, tables T_D and T_C are and will remain empty, then I do :
>> DELETE FROM T_A WHERE id_t_a = 1;
>>
>> I get this error :
>> foreign key mismatch - "T_D" referencing "T_C"
>>
>
> This script runs without error in the shell.
>
> The error indicates that there is no index t_c(id_t_c). Which is not the
> case in the above - it's an INTEGER PRIMARY KEY. Maybe there is a typo in
> the actual db schema that you fixed when transcribing. Or it could be a bug
> in some older version of SQLite that has been fixed since.
>
> Try opening the db with the shell tool and running ".lint fkey" - it will
> tell you if it thinks there is an index missing. Also check the entry for
> table T_C in the sqlite_master table - there might be a typo in the
> declaration of column id_t_c.
>
> Dan.
>
>
>
> ___
> 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

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

[Dan Kennedy]

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 (correct
me if I'm wrong).

I've renamed everything, hope this will be readable :

PRAGMA foreign_keys = true;

CREATE TABLE T_A (
id_t_a INTEGER PRIMARY KEY,
label TEXT
);

CREATE TABLE T_B (
id_t_b INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
label TEXT,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE
);

CREATE TABLE T_C (
id_t_c INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN_KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);

CREATE TABLE T_D (
id_t_d INTEGER PRIMARY KEY,
fk_t_c_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_c_id) REFERENCES T_C(id_t_c) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);

And then I'm doing this :

INSERT INTO T_A(label) VALUES("A1");   // id_t_a -> 1
INSERT INTO T_B(label, fk_t_a_id) VALUES("B1", 1);
INSERT INTO T_B(label, fk_t_a_id) VALUES("B2", 1);

At this point, tables T_D and T_C are and will remain empty, then I do :
DELETE FROM T_A WHERE id_t_a = 1;

I get this error :
foreign key mismatch - "T_D" referencing "T_C"


This script runs without error in the shell.

The error indicates that there is no index t_c(id_t_c). Which is not the 
case in the above - it's an INTEGER PRIMARY KEY. Maybe there is a typo 
in the actual db schema that you fixed when transcribing. Or it could be 
a bug in some older version of SQLite that has been fixed since.


Try opening the db with the shell tool and running ".lint fkey" - it 
will tell you if it thinks there is an index missing. Also check the 
entry for table T_C in the sqlite_master table - there might be a typo 
in the declaration of column id_t_c.


Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key mismatch with ON DELETE clauses

[Bubu Bubu]

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 everything, hope this will be readable :

PRAGMA foreign_keys = true;

CREATE TABLE T_A (
id_t_a INTEGER PRIMARY KEY,
label TEXT
);

CREATE TABLE T_B (
id_t_b INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
label TEXT,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE
);

CREATE TABLE T_C (
id_t_c INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN_KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);

CREATE TABLE T_D (
id_t_d INTEGER PRIMARY KEY,
fk_t_c_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_c_id) REFERENCES T_C(id_t_c) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);

And then I'm doing this :

INSERT INTO T_A(label) VALUES("A1");   // id_t_a -> 1
INSERT INTO T_B(label, fk_t_a_id) VALUES("B1", 1);
INSERT INTO T_B(label, fk_t_a_id) VALUES("B2", 1);

At this point, tables T_D and T_C are and will remain empty, then I do :
DELETE FROM T_A WHERE id_t_a = 1;

I get this error :
foreign key mismatch - "T_D" referencing "T_C"

My bet is on the combination of the ON DELETE clauses, but I'm not sure
where it's wrong.
As i said, T_D and T_C are empty anyway at this point so I don't even
understand why they are mentionned in the error message.

If you guys have questions go ahead, I hope this is clearly expressed.

Regards
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key constraint failure

[Mark Wagner]

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 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 (123);
> INSERT INTO t2 (id, id1) VALUES (456, 123);
>
> -- error: FOREIGN KEY constraint failed
> DELETE FROM t1
>
> OK, error is reproduced.
>
> Now you want to know which foreign key has failed:
>
> PRAGMA foreign_keys = OFF;
> BEGIN TRANSACTION;
> DELETE FROM t1 -- no error this time
> PRAGMA foreign_key_check
> -- table:"t2" rowid:456 parent:"t1" fkid:0
>
> This means that row 456 of table t2 has a broken foreign to table t1.
>
> If you want to know which row in t1 can not be deleted:
>
> PRAGMA foreign_key_list(t2)
> -- id:0 seq:0 table:"t1" from:"id1" to:"id" on_update:"NO ACTION"
> on_delete:"RESTRICT" match:"NONE"
>
> OK so id1 in table t2 gives the id of the t1 row which can not be deleted:
>
> SELECT id1 FROM t2 WHERE id = 456
> -- id1:123
>
> This is row 123 of t1 which can not be deleted.
>
> Make sure to rollback the failed transaction, and restore foreign key
> checks:
>
> ROLLBACK
> PRAGMA foreign_keys = ON
>
> Gwendal Roué
>
> > Le 10 mai 2017 à 06:57, Mark Wagner  a écrit :
> >
> > 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.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

Re: [sqlite] foreign key constraint failure

[Gwendal Roué]

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 (123);
INSERT INTO t2 (id, id1) VALUES (456, 123);

-- error: FOREIGN KEY constraint failed
DELETE FROM t1

OK, error is reproduced.

Now you want to know which foreign key has failed:

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
DELETE FROM t1 -- no error this time
PRAGMA foreign_key_check
-- table:"t2" rowid:456 parent:"t1" fkid:0

This means that row 456 of table t2 has a broken foreign to table t1.

If you want to know which row in t1 can not be deleted:

PRAGMA foreign_key_list(t2)
-- id:0 seq:0 table:"t1" from:"id1" to:"id" on_update:"NO ACTION" 
on_delete:"RESTRICT" match:"NONE"

OK so id1 in table t2 gives the id of the t1 row which can not be deleted:

SELECT id1 FROM t2 WHERE id = 456
-- id1:123

This is row 123 of t1 which can not be deleted.

Make sure to rollback the failed transaction, and restore foreign key checks:

ROLLBACK
PRAGMA foreign_keys = ON

Gwendal Roué

> Le 10 mai 2017 à 06:57, Mark Wagner  a écrit :
> 
> 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.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

Re: [sqlite] foreign key constraint failure

[nomad]

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 foreign key failures, not of their
> origin.

Maybe; If you are developing and can afford to reload/reset invalid
data you can turn off foreign key support and do the DELETE, and then
run the foreign_key_check pragma:

CREATE TABLE a(
id INTEGER PRIMARY KEY
);

CREATE TABLE b(
id INTEGER,
FOREIGN KEY(id) REFERENCES a(id)
);

INSERT INTO a VALUES(1);
INSERT INTO b VALUES(1);
DELETE FROM a;
-- Error: FOREIGN KEY constraint failed

PRAGMA foreign_keys=0;
DELETE FROM a;
-- No Error, but now your data is invalid

PRAGMA foreign_key_check;
--  table   rowid   parent  fkid
--  --  --  --  --
--  b   1   a   0

You could perhaps even run the DELETE inside a transaction and rollback
once you have obtained the information you need, to keep your data
integrity.

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key constraint failure

[Clemens Ladisch]

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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign key constraint failure

[Mark Wagner]

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.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign key referencing an undefined table?

[Olivier Mascia]

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 mean to catch this anytime before actually using the table T?

The current behavior is certainly useful (and maybe adhering to standard(s)) 
because it allows to declare table T before declaring table X and might solve 
some cross-reference between both.  Yet I'd feel happy to "schema_check" for 
such discrepancies.  I can code some automated checks around querying 
sqlite_master and pragma foreign_key_list, but isn't there any other way to 
proceed which I would have missed?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Keith Medcalf]

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 theoretical justification.

I believe that Codd originally referred to this as the "Domain" requirement.  
That is, that a "Parent" specified a domain, and that each "child" (member) 
must be a part of that domain.  Hence the requirement for the "Domain" (Parent) 
entries to be unique whilst the Child (Member of domain) entries are not, yet 
have a referent (foreign key) to the relation specifying the domain.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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 papers
specifically rejected pointers as a database mechanism, and the
relational model relies entirely on values.  That there are no pointers
is by design.  

I accept that SQL foreign keys require a unique referent.  I have to;
it's a fact.  

I'm not convinced that's good or necessary.  I can believe it's
convenient, in the same way that "bag theory" is convenient to avoid
ensuring uniqueness in SQL.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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 normalized the data properly
> (or sufficiently).

If you say so.  If by definition "sufficient" normalization has all FK
relationships as 1:N, then by definition any design without that
property is not sufficiently normalized.  

I tried with my relations P & C to show relation B is unnecessary.  If
you can show updating such a database would lead to update anomalies
without B defined, I'd be interested to see that.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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 all rows except the "last one"
> for each distinct (=UNIQUE) foreign key combination in the child
> table. This is not a set operation

First, I don't recognize "parent" and "child" as relational concepts.
It's convenient shorthand for us, but we must not let that terminology
contaminate our thinking.  

You seem to think M:N represents some kind of logical challenge that
1:N does not.  Why?  Many databases have a rule similar to "every order
has line items, and every line item has an order".  On deletion of the
last "child", delete the "parent".  It is not the difference between
many and one that matters; it's the difference between any and none.  

In an M:N relationship, cascading delete probably doesn't make sense.
But it wouldn't make more sense represented as three tables instead of 
two!  

Please consider my example again:

P(A,B) and C(C,B) where C(B) references A(B) 
and A(B) is not unique 

and 

B(B), P(A,B), and C(C,B) 
where P(B) and C(B) both reference B(B) 

In neither case can you define a cascading relationship between P and
C.  Adding B doesn't change that.  It doesn't make the design more
"normalized".  

What it does do is make it more "SQLized"; it allows the use of SQL FK
declarations to enforce that B(B) exists so long as P(B) or C(B) does.  

By requiring a unique referent, though, SQL prevents declaration of the
rule, "for every C(B), there must be a P(B)".  

You might well answer that the relationship should be 

P(A,B) and C(C,A,B) where C(A,B) references A(A,B) 

because, otherwise, what B do we mean?  And that's effectively what I
did with the design that motivated my original question.  

But I'm not convinced it's necessary.  In my case, C(A) can be
derived from another relationship; C(B) adds information to that A and
cannot be related to some other A.  The "which B" question can be
answered by a join.  Unambiguously.  I'm schlepping C(A) around only
for SQL reasons, not for any logical one I can see.  

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 theoretical justification.  

--jkl








___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Hick Gunter]

I was not asking for SQL statements but that you think about what you are 
trying to achieve.

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 all 
rows except the "last one" for each distinct (=UNIQUE) foreign key combination 
in the child table. This is not a set operation, there is no "last row" in a 
set. Likewise ON DELETE CASCADE needs tob e triggered when the "last row" for a 
distinct (=UNIQUE) foreign key combination is deleted from the parent table. 
Again, there is no "last row" in a set.

Not requiring UNIQUE means that FK enforcement relies on sets exhibiting an 
order, when they are clearly not allowed to have one by the relational model. 
Thus UNIQUE is required. (Proof by "reductio ad absurdum")

SQL for non-unique foreign key ON DELETE CASCADE:

-- assumes FK constraints are DEFERRED
BEGIN;
-- get the set of foreign key expressions touched by the delete
CREATE TEMP TABLE TBD_C AS SELECT DISTINCT  FROM P WHERE ;
-- remove the rows from parent table
DELETE FROM P WHERE ;
-- remove foreign key expressions still fulfilled by parent table
DELETE FROM TBD_C WHERE EXISTS (SELECT 1 FROM P WHERE );
-- remove "unparented" rows from child table
DELETE FROM C WHERE  IN (SELECT * FROM TBD_C);
--
COMMIT;


-Ursprü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 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 provided the relevant text, so we know SQLite conforms 
to the standard in this regard.  Thank you, Kees.

To answer your questions, supposing UNIQUE were not required for a FK referent:

> Which, if any, of the P rows can you safely delete without losing
> referential integrity?

delete from P where not exists (
select 1 from C where color = P.color and shape = P.shape );

> Assuming ON DELETE CASCADE, when do you delete the rows from C?

delete from C where exists (
select 1 from P where color = C.color and shape = C.shape
and /* ... P criteria ... */
);

From a theoretical standpoint, for relations

P{A,B}
and
C{C,B} with C{B} referencing P{B}

without loss of information we may add

B{B}

and constraints

P{B} references B{B} and C{B} references B{B}

But, having added B, we have not added any information.  We have merely added 
another relation that is the projection of P{B}.

It is *usually* true that B will have other attributes, in which case of course 
a B table would be needed to hold them.  Maybe that, or practical 
considerations, or both, motivated the SQL rule.

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Keith Medcalf]

> > 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
> conforms to the standard in this regard.  Thank you, Kees.
> 
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
> 
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
> 
> delete from P where not exists (
>   select 1 from C where color = P.color and shape = P.shape );
> 
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
> 
> delete from C where exists (
>   select 1 from P where color = C.color and shape = C.shape
>   and /* ... P criteria ... */
> );
> 
> From a theoretical standpoint, for relations
> 
>   P{A,B}
> and
>   C{C,B} with C{B} referencing P{B}
> 
> without loss of information we may add
> 
>   B{B}
> 
> and constraints
> 
>   P{B} references B{B} and C{B} references B{B}
> 
> But, having added B, we have not added any information.  We have
> merely added another relation that is the projection of P{B}.
> 
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them.  Maybe that, or
> practical considerations, or both, motivated the SQL rule.

It has always been the case in SQL (and relational databases in general) that 
Parents must be unique.  It was even the case in Hierarchical databases that 
Parents of a Set must be unique -- that is, identify a 1:N relationship.  N:M 
relationship modelling requires a connecting N:M table where the relationships 
between table A -> C <- B are one parent in A identifies N records in C, each 
of which identifies one record in B (that is, A -> C is 1:N and B -> C is 1:M 
with the resulting A <-> B relationship being N:M).  Only Network Model (I 
forget whether it has to be Network Extended -- it has been a long time since I 
used one) Hierarchical databases model N:M sets directly.

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 
normalized the data properly (or sufficiently).




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[J Decker]

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. 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
>> conforms to the standard in this regard.  Thank you, Kees.
>>
>> To answer your questions, supposing UNIQUE were not required for a
>> FK referent:
>>
>> > Which, if any, of the P rows can you safely delete without losing
>> > referential integrity?
>>
>
> 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.  If the parent structure pointing at
> a child is no longer, how is the child valid for anything other than
> garbage collection?
>
> okay but I guess that's what on delete SETNULL is for... so you can keep
the child as a special 'NULL' record for later searching just by file


> If the unique; (memory address) key contains multiple parts, then you have
> to reference all aprts.  If there was a part that was itself unique then
> you wouldn't really need the second part; otherwise you do in order to
> differentiate (1,1) from (1,2)
>
> perhaps what you intend is to use a graph database, where a single 'child'
> might be referred to from mulitple parents (links) ; but then your model
> should be something more self recursive like...
> (pseudoSQL)
> create table inode( iNode char PRIMARY KEY, moreColumns char,  )
> create table inodeLinks( someInode char, someOtherInode char, FK(someinode
> references inode.iNode), FK(someOtheriNode references inode.iNode) )
>
> create table inodeFileData( iNode char , fileDataIfAny char,  )
> create table inodeNameData( iNode char, theNameOfThisPoint char,  )
> create table inodeFileData( iNode char, attributes char,  )
>
> Such that any directory might also contain some data, but any file can
> contain other files since each thing is named separatly from it's key.
>
>
>
>
> Instead of having the file records themselves having their directory, why
> not just make a separate table
> ( directory char , hasFiles char );
>
> CTE queries can make this even tolerable for linear queries
> 
> Sorry I wandered into an alternative solution instead of the other
> representation...
> in .NET you have DataSet, whichi contains DataTables, and between DTs are
> Relation(s).  When you fill a dataset with all properly keyed things, the
> row in one table (files).getChildRows( "filesInDirectory" ) (returns more
> rows)
>
> And it's really a direct reference, it doesn't have to go additionally
> search through the index in the child table to get to the rows by looking
> up a key, it just points to it.  Much like working with a graph, your
> master records just point at the children, and you never have to search for
> anything...
>
>
>
>> delete from P where not exists (
>> select 1 from C where color = P.color and shape = P.shape );
>>
>> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>>
>> delete from C where exists (
>> select 1 from P where color = C.color and shape = C.shape
>> and /* ... P criteria ... */
>> );
>>
>> From a theoretical standpoint, for relations
>>
>> P{A,B}
>> and
>> C{C,B} with C{B} referencing P{B}
>>
>> without loss of information we may add
>>
>> B{B}
>>
>> and constraints
>>
>> P{B} references B{B} and C{B} references B{B}
>>
>> But, having added B, we have not added any information.  We have
>> merely added another relation that is the projection of P{B}.
>>
>> It is *usually* true that B will have other attributes, in which case
>> of course a B table would be needed to hold them.  Maybe that, or
>> practical considerations, or both, motivated the SQL rule.
>>
>> --jkl
>> ___
>> 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

Re: [sqlite] foreign key cardinality

[J Decker]

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, ...) that references P
> > (color,shape)
>
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard.  Thank you, Kees.
>
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
>
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
>

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.  If the parent structure pointing at
a child is no longer, how is the child valid for anything other than
garbage collection?

If the unique; (memory address) key contains multiple parts, then you have
to reference all aprts.  If there was a part that was itself unique then
you wouldn't really need the second part; otherwise you do in order to
differentiate (1,1) from (1,2)

perhaps what you intend is to use a graph database, where a single 'child'
might be referred to from mulitple parents (links) ; but then your model
should be something more self recursive like...
(pseudoSQL)
create table inode( iNode char PRIMARY KEY, moreColumns char,  )
create table inodeLinks( someInode char, someOtherInode char, FK(someinode
references inode.iNode), FK(someOtheriNode references inode.iNode) )

create table inodeFileData( iNode char , fileDataIfAny char,  )
create table inodeNameData( iNode char, theNameOfThisPoint char,  )
create table inodeFileData( iNode char, attributes char,  )

Such that any directory might also contain some data, but any file can
contain other files since each thing is named separatly from it's key.




Instead of having the file records themselves having their directory, why
not just make a separate table
( directory char , hasFiles char );

CTE queries can make this even tolerable for linear queries

Sorry I wandered into an alternative solution instead of the other
representation...
in .NET you have DataSet, whichi contains DataTables, and between DTs are
Relation(s).  When you fill a dataset with all properly keyed things, the
row in one table (files).getChildRows( "filesInDirectory" ) (returns more
rows)

And it's really a direct reference, it doesn't have to go additionally
search through the index in the child table to get to the rows by looking
up a key, it just points to it.  Much like working with a graph, your
master records just point at the children, and you never have to search for
anything...



> delete from P where not exists (
> select 1 from C where color = P.color and shape = P.shape );
>
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>
> delete from C where exists (
> select 1 from P where color = C.color and shape = C.shape
> and /* ... P criteria ... */
> );
>
> From a theoretical standpoint, for relations
>
> P{A,B}
> and
> C{C,B} with C{B} referencing P{B}
>
> without loss of information we may add
>
> B{B}
>
> and constraints
>
> P{B} references B{B} and C{B} references B{B}
>
> But, having added B, we have not added any information.  We have
> merely added another relation that is the projection of P{B}.
>
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them.  Maybe that, or
> practical considerations, or both, motivated the SQL rule.
>
> --jkl
> ___
> 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

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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 provided the relevant text, so we know SQLite
conforms to the standard in this regard.  Thank you, Kees.  

To answer your questions, supposing UNIQUE were not required for a
FK referent: 

> Which, if any, of the P rows can you safely delete without losing
> referential integrity? 

delete from P where not exists ( 
select 1 from C where color = P.color and shape = P.shape );

> Assuming ON DELETE CASCADE, when do you delete the rows from C?

delete from C where exists ( 
select 1 from P where color = C.color and shape = C.shape 
and /* ... P criteria ... */ 
);

From a theoretical standpoint, for relations

P{A,B}
and
C{C,B} with C{B} referencing P{B}

without loss of information we may add

B{B} 

and constraints

P{B} references B{B} and C{B} references B{B}

But, having added B, we have not added any information.  We have
merely added another relation that is the projection of P{B}.  

It is *usually* true that B will have other attributes, in which case
of course a B table would be needed to hold them.  Maybe that, or
practical considerations, or both, motivated the SQL rule.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Kees Nuyt]

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 can't check against my usual
> resources.  

Here you go:

Information Technology - Database Language SQL
(Proposed revised text of DIS 9075)
(Second Informal Review Draft) ISO/IEC 9075:1992, 
Database Language SQL- July 30, 1992
which is pretty close to the final text.

X3H2-92-154/DBL CBR-002  (page 274 or thereabout)

11.8  

Function

Specify a referential constraint.

Format

 ::=
 FOREIGN KEY   
   

 ::=
 REFERENCES 
   [ MATCH  ]
   [  ]

 ::=
   FULL
 | PARTIAL

 ::=
 

 ::=
  []

 ::= 

 ::=
[  ]
 |  [  ]

 ::= ON UPDATE 

 ::= ON DELETE 

 ::=
   CASCADE
 | SET NULL
 | SET DEFAULT
 | NO ACTION


Syntax Rules

1) Let referencing table be the table identified by the containing
or . Let referenced
   table be the table identified by the  in the . Let referencing columns be the column
   or columns identified by the  in the
and let referencing column be one such
   column.

2) Case:

   a) If the  specifies a , then the set of column names of that  shall be equal to the set of column names
 in the unique columns of a unique constraint of the refer-
 enced table. Let referenced columns be the column or columns
 identified by that  and let refer-
 enced column be one such column. Each referenced column shall
 identify a column of the referenced table and the same column
 shall not be identified more than once.

   b) If the  does not specify a
 , then the table descriptor of the
 referenced table shall include a unique constraint that spec-
 ifies PRIMARY KEY. Let referenced columns be the column or
 columns identified by the unique columns in that unique con-
 straint and let referenced column be one such column. The
  shall be considered to implic-
 itly specify a  that is identical to
 that .

3) The table constraint descriptor describing the  whose  identifies the
   referenced columns shall indicate that the unique constraint is
   not deferrable.


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Hick Gunter]

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)

Now insert some rows into P (red,square,none, ...) and (red, square, bold, ...).

And insert some rows into C (1,...,red, square, ...) and (2,...,red, 
square,...).

Which, if any, of the P rows can you safely delete without losing referential 
integrity? Assuming ON DELETE CASCADE, when do you delete the rows from C?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto: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 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, 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 can't 
check against my usual resources.

I now see how to solve the conundrum I faced that motivated my complaint.  It 
requires more columns and UNIQUE constraints than I think are strictly 
necessary, but it can be made to work.

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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, 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 can't check against my usual
resources.  

I now see how to solve the conundrum I faced that motivated my
complaint.  It requires more columns and UNIQUE constraints than I
think are strictly necessary, but it can be made to work.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Hick Gunter]

If a is already unique, there is no need for b in the primary key...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Simon Slavin
Gesendet: Dienstag, 28. Februar 2017 01:41
An: SQLite mailing list 
Betreff: 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)); 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 two rows in that table which both look like they may be 
the parent.

So you need a UNIQUE index on the reference column(s).  Of course, if they’re 
the primary key then they already have a UNIQUE index, since the primary key of 
a table must be unique and SQLite automatically makes an index to enforce that. 
 So try instead

create table A(a, b, primary key (a,b)); CREATE UNIQUE INDEX A_a ON A (a); 
create table C(c references A(a));

A way to have this index automatically created is to put a UNIQUE requirement 
on that column:

create table A(a, b, primary key (a UNIQUE,b)); create table C(c references 
A(a));

This is documented in section 3 of

<https://www.sqlite.org/foreignkeys.html>

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Hick Gunter]

Your assumption does not correspond with the documentation, see 
http://sqlite.org/foreignkeys.html :

"The parent key is the column or set of columns in the parent table that the 
foreign key constraint refers to. This is normally, but not always, the primary 
key of the parent table. The parent key must be a named column or columns in 
the parent table, not the rowid."

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

"Cardinality" refers to the number of fields in the constraint. As in "(x,y,z) 
of child table must match (a,b,c) of parent table" is valid, while "(x,y) of 
child must match (a) of parent table" is invalid, just like "(x,y) of chid 
table must match (a,b,c) of parent table".


-Ursprüngliche 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 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 brevity):

Directory ( name primary key )
File ( name, dname references Directory(name), primary key (name, 
dname) ) Program ( name primary key )

Note that a filename is not unique except within a directory.

Now I have authorization tables that constrain what directories and files a 
program can access, and in what order.

ProgDir (  pname references Program(name),
dname references Directory(name),
ord,
primary key (pname, ord) )

ProgFile ( pname, pord, fname, ord,
   primary key (pname, pord, fname),
   foreign key (pname, pord) references ProgDir (pname, 
ord), foreign key (fname) references File(name) ) -- ouch

A program can access a directory and that directory's files.  The file access 
order depends on which directory we're referring to, and requires that the 
program be permitted to use that directory.

It's not necessary to carry dname in ProgFile; it can be derived from 
ProgDir.dname.  But it would be nice to know that the ProgFile.fname exists in 
File.name.

If I added Progfile.dname, so that I could declare a foreign key to File, it 
would be nice to also add it to the FK declaration referring to ProgDir:

foreign key (pname, pord, dname) references ProgDir(pname, ord,
dname)

because that reflects the rule that permission to use a file requires 
permission to use the file's directory.

In both cases, I'm stuck.  SQLite requires a FK declaration to match the 
referrenced table's PK, else you get a "foreign key mismatch"
error.  (At least, that's my understanding of, "Parent and child keys must have 
the same cardinality." If ProgFile has no dname, it can't have a foreign key to 
File. If it does have dname, it can't include it in its reference to ProgDir.

The relational model doesn't recognize the concept of "primary key", and the 
rule that a foreign key must refer to a primary key is not part of the SQL 
standard.  The rule is unnecessary, and interferes with accurate foreign key 
reference declarations.

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Simon Slavin]

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 they may be 
the parent.

So you need a UNIQUE index on the reference column(s).  Of course, if they’re 
the primary key then they already have a UNIQUE index, since the primary key of 
a table must be unique and SQLite automatically makes an index to enforce that. 
 So try instead

create table A(a, b, primary key (a,b));
CREATE UNIQUE INDEX A_a ON A (a);
create table C(c references A(a));

A way to have this index automatically created is to put a UNIQUE requirement 
on that column:

create table A(a, b, primary key (a UNIQUE,b));
create table C(c references A(a));

This is documented in section 3 of



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[James K. Lowden]

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 references A(a));
sqlite> insert into A values (1,2);
sqlite> insert into C values (1);
Error: foreign key mismatch - "C" referencing "A"
sqlite> 

Clearly something about the primary key influences the evaluation of a
referencing foreign key.  Perhaps i should have said "whole primary
key"?  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Ben Newberg]

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, b integer unique, c text);
sqlite> insert into x (a, b, c) values (1, 11, 'a');
sqlite> insert into x (a, b, c) values (2, 22, 'a');
sqlite> insert into x (a, b, c) values (3, 33, 'b');
sqlite> create table y1 (a integer references x(a));
sqlite> insert into y1 values(1);
sqlite> create table y2 (b integer references x(b));
sqlite> insert into y2 values(11);
sqlite> create table y3 (c text references x(c));
sqlite> insert into y3 values('a');
Error: foreign key mismatch - "y3" referencing "x"
sqlite> insert into y3 values('b');
Error: foreign key mismatch - "y3" referencing "x"
sqlite>

On Mon, Feb 27, 2017 at 3:07 PM, 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?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] foreign key cardinality

[Richard Hipp]

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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign key cardinality

[James K. Lowden]

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 brevity):

Directory ( name primary key )
File ( name, dname references Directory(name), primary key
(name, dname) ) Program ( name primary key )

Note that a filename is not unique except within a directory.

Now I have authorization tables that constrain what directories and
files a program can access, and in what order.  

ProgDir (  pname references Program(name),
dname references Directory(name),
ord, 
primary key (pname, ord) )

ProgFile ( pname, pord, fname, ord,
   primary key (pname, pord, fname),
   foreign key (pname, pord) references ProgDir
(pname, ord), foreign key (fname) references File(name) ) -- ouch

A program can access a directory and that directory's files.  The file
access order depends on which directory we're referring to, and
requires that the program be permitted to use that directory.

It's not necessary to carry dname in ProgFile; it can be derived from
ProgDir.dname.  But it would be nice to know that the ProgFile.fname
exists in File.name.

If I added Progfile.dname, so that I could declare a foreign key to
File, it would be nice to also add it to the FK declaration referring
to ProgDir:

foreign key (pname, pord, dname) references ProgDir(pname, ord,
dname)

because that reflects the rule that permission to use a file requires
permission to use the file's directory.

In both cases, I'm stuck.  SQLite requires a FK declaration to match
the referrenced table's PK, else you get a "foreign key mismatch"
error.  (At least, that's my understanding of, "Parent and child keys
must have the same cardinality." If ProgFile has no dname, it can't
have a foreign key to File. If it does have dname, it can't include it
in its reference to ProgDir.  

The relational model doesn't recognize the concept of "primary key",
and the rule that a foreign key must refer to a primary key is not part
of the SQL standard.  The rule is unnecessary, and interferes with
accurate foreign key reference declarations.

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FOREIGN KEY question

[J Decker]

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
>
>   CREATE TABLE t (
> x PRIMARY KEY,
> y CONSTRAINT this_is_the_name REFERENCES t(x)
>   );
>
> But it is ignored:
>
>   > PRAGMA foreign_key_list(t);
>   0|0|t|y|x|NO ACTION|NO ACTION|NONE
>   > INSERT INTO t VALUES (1, 2);
>   Error: FOREIGN KEY constraint failed
>
> the value 2 you set for Y doesn't exsit as an X in t.


>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] FOREIGN KEY question

[Clemens Ladisch]

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

But it is ignored:

  > PRAGMA foreign_key_list(t);
  0|0|t|y|x|NO ACTION|NO ACTION|NONE
  > INSERT INTO t VALUES (1, 2);
  Error: FOREIGN KEY constraint failed


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FOREIGN KEY question

[Simon Slavin]

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.



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FOREIGN KEY question

[Hick Gunter]

Did you look at the syntax diagrams? If you mean giving names to foreign key 
clauses, then no.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Korot
Gesendet: Mittwoch, 08. Februar 2017 06:04
An: Discussion of SQLite 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://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FOREIGN KEY question

[Igor Korot]

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

[Ken Wagner]

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 
in multiple places (in the second case).

_The Power of Love_ was recorded by Frankie Goes to Hollywood, Jennifer Rush, 
and Huey Lewis and The News.  Not only are these different recordings, they’re 
different songs.

Nor can you place a UNIQUE requirement on the combination of (title,authors).  
There are three different /studio/ edits of Bowie’s _Heroes_, and two of them 
are different enough that someone might like one and not the other.  Not to 
mention numerous different live versions which appear on convert albums and 
DVDs.

Simon.
___
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

Re: [sqlite] Foreign key error...

[Simon Slavin]

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 to Hollywood, Jennifer Rush, 
and Huey Lewis and The News.  Not only are these different recordings, they’re 
different songs.

Nor can you place a UNIQUE requirement on the combination of (title,authors).  
There are three different /studio/ edits of Bowie’s _Heroes_, and two of them 
are different enough that someone might like one and not the other.  Not to 
mention numerous different live versions which appear on convert albums and 
DVDs.

Simon.
___
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 Medcalf]

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

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.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 trackno seems to work. Thus results cannot be
> guaranteed valid?
> 
> Ken
> 
> 
> On 01/08/2017 06:57 AM, Keith Medcalf wrote:
> > On Sunday, 8 January, 2017 05:05, Ken Wagner 
> wrote:
> >
> >> Keith,
> >>
> >> Ahh, better to be specific and avoid simplistic assumptions.
> >>
> >> For foreign keys which is better: 'trackerartist' or 'artistid' in the
> >> track file? Does it matter? Is it personal preference?
> > It is a matter of personal preference.  Personally, I use the same name
> for the same data, and do not add useless prefaces, and usually do not
> preface the id with the table name.  For example:
> >
> > create table artists (
> >   id integer primary key,
> >   name text collate nocase unique
> > );
> > create table albums (
> >   id integer primary key,
> >   name text collate nocase unique,
> >   artistid integer references artists
> > );
> > create table tracks (
> >   id integer primary key,
> >   seq integer,
> >   name text collate nocase,
> >   artistid integer references artists,
> >   albumid integer references albums,
> >   unique (albumid, seq)
> > );
> >
> > select albums.name as albumname,
> > albumartists.name as albumartist,
> > tracks.seq as trackno,
> > tracks.name as trackname,
> > trackartists.name as trackartist
> >from albums, tracks, artists as albumartists, artists as trackartists
> >   where tracks.artistid = trackartists.id
> > and tracks.albumid = albums.id
> > and albums.artistid = albumartists.id;
> >
> > Of course, this does not allow the same track on multiple albums.  For
> that you need another table to do the N:M mapping:
> >
> > create table artists (
> >   id integer primary key,
> >   name text collate nocase unique
> > );
> > create table albums (
> >   id integer primary key,
> >   name text collate nocase unique,
> >   artistid integer references artists
> > );
> > create table tracks (
> >   id integer primary key,
> >   name text collate nocase,
> >   artistid integer references artists,
> > );
> > create table albumtracks (
> >   id integer primary key,
> >   albumid integer references albums,
> >   trackid integer references tracks,
> >   seq integer,
> >   unique (albumid, seq),
> >   unique (albumid, trackid),
> >   unique (trackid, albumid)
> > );
> >
> > select albums.name as albumname,
> > albumartists.name as akbumartist,
> > albumtracks.seq as trackno,
> > tracks.name as trackname,
> > trackartists.name as trackartist
> >from albums, albumtracks, tracks, artists as albumartists, artists as
> trackartists
> >   where tracks.artistid = trackartists.id
> > and albumtracks.albumid = albums.id
> > and albumtracks.trackid = tracks.id
> > and albums.artistid = albumartists.id;
> >
> >> On 01/08/2017 05:46 AM, Keith Medcalf wrote:
> >>> ... join ... using (column) has nothing whatever to do with foreign
> >> keys.
> >>> "FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is
> >> sweetening and full of calories, but has no nutritional value) for the
> >> expression "FROM a, b WHERE a.c = b.c"
> >>> This is so, for example, if you use really long stupid names it saves
> >> considerable space and typing:
> >>> SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined
> >> USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
> >>> -vs-
> >>> SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE
> >>
> TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTabl
> >> e =
> >>
> TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTab
> >> le;
> >>>
> >>>> -Original Message

Re: [sqlite] Foreign key error...

[David Raymond]

On the foreign key page (http://www.sqlite.org/foreignkeys.html) at the very 
end of section 3 is has:

CREATE TABLE artist(
  artistidINTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);

The block above uses a shorthand form to create the foreign key constraint. 
Attaching a "REFERENCES " clause to a column definition creates a 
foreign key constraint that maps the column to the primary key of 
. Refer to the CREATE TABLE documentation for further details.



In the create table page if you expand column-def, then column-constraint, then 
foreign-key-clause, you can see there's a flow path that skips the column names 
of the parent table. Unfortunately I don't see any explainatory text on the 
page for what that actually 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 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
___
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

Re: [sqlite] Foreign key error...

[James K. Lowden]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

[Ken Wagner]

Keith,

"this does not allow the same track on multiple albums" with the same trackno, 
but a different trackno seems to work. Thus results cannot be guaranteed valid?

Ken


On 01/08/2017 06:57 AM, Keith Medcalf wrote:

On Sunday, 8 January, 2017 05:05, Ken Wagner  wrote:


Keith,

Ahh, better to be specific and avoid simplistic assumptions.

For foreign keys which is better: 'trackerartist' or 'artistid' in the
track file? Does it matter? Is it personal preference?

It is a matter of personal preference.  Personally, I use the same name for the 
same data, and do not add useless prefaces, and usually do not preface the id 
with the table name.  For example:

create table artists (
  id integer primary key,
  name text collate nocase unique
);
create table albums (
  id integer primary key,
  name text collate nocase unique,
  artistid integer references artists
);
create table tracks (
  id integer primary key,
  seq integer,
  name text collate nocase,
  artistid integer references artists,
  albumid integer references albums,
  unique (albumid, seq)
);

select albums.name as albumname,
albumartists.name as albumartist,
tracks.seq as trackno,
tracks.name as trackname,
trackartists.name as trackartist
   from albums, tracks, artists as albumartists, artists as trackartists
  where tracks.artistid = trackartists.id
and tracks.albumid = albums.id
and albums.artistid = albumartists.id;

Of course, this does not allow the same track on multiple albums.  For that you 
need another table to do the N:M mapping:

create table artists (
  id integer primary key,
  name text collate nocase unique
);
create table albums (
  id integer primary key,
  name text collate nocase unique,
  artistid integer references artists
);
create table tracks (
  id integer primary key,
  name text collate nocase,
  artistid integer references artists,
);
create table albumtracks (
  id integer primary key,
  albumid integer references albums,
  trackid integer references tracks,
  seq integer,
  unique (albumid, seq),
  unique (albumid, trackid),
  unique (trackid, albumid)
);

select albums.name as albumname,
albumartists.name as akbumartist,
albumtracks.seq as trackno,
tracks.name as trackname,
trackartists.name as trackartist
   from albums, albumtracks, tracks, artists as albumartists, artists as 
trackartists
  where tracks.artistid = trackartists.id
and albumtracks.albumid = albums.id
and albumtracks.trackid = tracks.id
and albums.artistid = albumartists.id;


On 01/08/2017 05:46 AM, Keith Medcalf wrote:

... join ... using (column) has nothing whatever to do with foreign

keys.

"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is

sweetening and full of calories, but has no nutritional value) for the
expression "FROM a, b WHERE a.c = b.c"

This is so, for example, if you use really long stupid names it saves

considerable space and typing:

SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined

USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);

-vs-
SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE

TheFirstTableToBeJoined.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: [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 informative?

I.e., wherever it is seen it shows the track-artist link? But is more
demanding when coding:

   'on trackerartist = artistid' vs 'using (artistid)'

Best or preferred SQLite3 practice for using which foreign reference
style 'trackartist' vs 'artistid'?

Thanks,
Ken


On 01/08/2017 04:47 AM, Kees Nuyt wrote:

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.org/foreignkeys.html was observed.

It appears that 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
bbd85d235f7037c6a033a9690534391ffeacecc8

sqlite> select artistname, trackname from artist inner join track

using

(trackartist);
Error:\ cannot join using column trackartist - column n

Re: [sqlite] Foreign key error...

[Ken Wagner]

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:


... 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 b ON a.c = b.c".

Syntax does matter (to some?) for readability and comprehension of a query
IMHO.

I prefer to keep my WHERE clauses for single-table "filtering",
and rely on JOIN-ON for how two tables "connect" during a join.
(which columns to "thread the needle through" to form/constitute
a "multi-table row" is my personal mental image of a join).

My $0.02. --DD

PS: I myself consider "FROM a, b WHERE a.c = b.c" to be the "legacy"
syntax, best avoided :)
___
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

Re: [sqlite] Foreign key error...

[Dominique Devienne]

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 b ON a.c = b.c".

Syntax does matter (to some?) for readability and comprehension of a query
IMHO.

I prefer to keep my WHERE clauses for single-table "filtering",
and rely on JOIN-ON for how two tables "connect" during a join.
(which columns to "thread the needle through" to form/constitute
a "multi-table row" is my personal mental image of a join).

My $0.02. --DD

PS: I myself consider "FROM a, b WHERE a.c = b.c" to be the "legacy"
syntax, best avoided :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

[J Decker]

as an alternative you can use the same ID - nessecitating prefiixing the
primary id with the table name... but then natural join can work.
 (Although common words like 'name' should also be table-name prefixed).

Having a 'id' as the primary key disallows the using() shorthand also.

If all column names that are meant to be joined are the same name, database
tools can extrapolate relationships; although with defined foriegn keys
they should be able to manage with mismatched naming.

But I suspect that the example from the sqlite documentation that you're
starting with uses different names to illustrate what is actually meant to
be used in which positions for the foriegn key definition.



On Sun, Jan 8, 2017 at 4:57 AM, Keith Medcalf  wrote:

>
> On Sunday, 8 January, 2017 05:05, Ken Wagner  wrote:
>
> > Keith,
> >
> > Ahh, better to be specific and avoid simplistic assumptions.
> >
> > For foreign keys which is better: 'trackerartist' or 'artistid' in the
> > track file? Does it matter? Is it personal preference?
>
> It is a matter of personal preference.  Personally, I use the same name
> for the same data, and do not add useless prefaces, and usually do not
> preface the id with the table name.  For example:
>
> create table artists (
>  id integer primary key,
>  name text collate nocase unique
> );
> create table albums (
>  id integer primary key,
>  name text collate nocase unique,
>  artistid integer references artists
> );
> create table tracks (
>  id integer primary key,
>  seq integer,
>  name text collate nocase,
>  artistid integer references artists,
>  albumid integer references albums,
>  unique (albumid, seq)
> );
>
> select albums.name as albumname,
>albumartists.name as albumartist,
>tracks.seq as trackno,
>tracks.name as trackname,
>trackartists.name as trackartist
>   from albums, tracks, artists as albumartists, artists as trackartists
>  where tracks.artistid = trackartists.id
>and tracks.albumid = albums.id
>and albums.artistid = albumartists.id;
>
> Of course, this does not allow the same track on multiple albums.  For
> that you need another table to do the N:M mapping:
>
> create table artists (
>  id integer primary key,
>  name text collate nocase unique
> );
> create table albums (
>  id integer primary key,
>  name text collate nocase unique,
>  artistid integer references artists
> );
> create table tracks (
>  id integer primary key,
>  name text collate nocase,
>  artistid integer references artists,
> );
> create table albumtracks (
>  id integer primary key,
>  albumid integer references albums,
>  trackid integer references tracks,
>  seq integer,
>  unique (albumid, seq),
>  unique (albumid, trackid),
>  unique (trackid, albumid)
> );
>
> select albums.name as albumname,
>albumartists.name as akbumartist,
>albumtracks.seq as trackno,
>tracks.name as trackname,
>trackartists.name as trackartist
>   from albums, albumtracks, tracks, artists as albumartists, artists as
> trackartists
>  where tracks.artistid = trackartists.id
>and albumtracks.albumid = albums.id
>and albumtracks.trackid = tracks.id
>and albums.artistid = albumartists.id;
>
> > On 01/08/2017 05:46 AM, Keith Medcalf wrote:
> > > ... join ... using (column) has nothing whatever to do with foreign
> > keys.
> > >
> > > "FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is
> > sweetening and full of calories, but has no nutritional value) for the
> > expression "FROM a, b WHERE a.c = b.c"
> > >
> > > This is so, for example, if you use really long stupid names it saves
> > considerable space and typing:
> > >
> > > SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined
> > USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
> > > -vs-
> > > SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE
> > TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheF
> irstAndTheSecondTabl
> > e =
> > TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheF
> irstAndTheSecondTab
> > 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: [sqlite] Foreign key error...
> > >>
> > >> Thanks, Kees,
> &g

Re: [sqlite] Foreign key error...

[Keith Medcalf]

On Sunday, 8 January, 2017 05:05, Ken Wagner  wrote:

> Keith,
> 
> Ahh, better to be specific and avoid simplistic assumptions.
> 
> For foreign keys which is better: 'trackerartist' or 'artistid' in the
> track file? Does it matter? Is it personal preference?

It is a matter of personal preference.  Personally, I use the same name for the 
same data, and do not add useless prefaces, and usually do not preface the id 
with the table name.  For example:

create table artists (
 id integer primary key, 
 name text collate nocase unique
);
create table albums (
 id integer primary key, 
 name text collate nocase unique, 
 artistid integer references artists
);
create table tracks (
 id integer primary key, 
 seq integer,
 name text collate nocase, 
 artistid integer references artists, 
 albumid integer references albums,
 unique (albumid, seq)
);

select albums.name as albumname, 
   albumartists.name as albumartist, 
   tracks.seq as trackno, 
   tracks.name as trackname, 
   trackartists.name as trackartist
  from albums, tracks, artists as albumartists, artists as trackartists
 where tracks.artistid = trackartists.id
   and tracks.albumid = albums.id
   and albums.artistid = albumartists.id;

Of course, this does not allow the same track on multiple albums.  For that you 
need another table to do the N:M mapping:

create table artists (
 id integer primary key, 
 name text collate nocase unique
);
create table albums (
 id integer primary key, 
 name text collate nocase unique, 
 artistid integer references artists
);
create table tracks (
 id integer primary key, 
 name text collate nocase, 
 artistid integer references artists, 
);
create table albumtracks (
 id integer primary key,
 albumid integer references albums,
 trackid integer references tracks,
 seq integer,
 unique (albumid, seq),
 unique (albumid, trackid),
 unique (trackid, albumid)
);

select albums.name as albumname, 
   albumartists.name as akbumartist,
   albumtracks.seq as trackno, 
   tracks.name as trackname, 
   trackartists.name as trackartist
  from albums, albumtracks, tracks, artists as albumartists, artists as 
trackartists
 where tracks.artistid = trackartists.id
   and albumtracks.albumid = albums.id
   and albumtracks.trackid = tracks.id
   and albums.artistid = albumartists.id;

> On 01/08/2017 05:46 AM, Keith Medcalf wrote:
> > ... join ... using (column) has nothing whatever to do with foreign
> keys.
> >
> > "FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is
> sweetening and full of calories, but has no nutritional value) for the
> expression "FROM a, b WHERE a.c = b.c"
> >
> > This is so, for example, if you use really long stupid names it saves
> considerable space and typing:
> >
> > SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined
> USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
> > -vs-
> > SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE
> TheFirstTableToBeJoined.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: [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 informative?
> >>
> >> I.e., wherever it is seen it shows the track-artist link? But is more
> >> demanding when coding:
> >>
> >>   'on trackerartist = artistid' vs 'using (artistid)'
> >>
> >> Best or preferred SQLite3 practice for using which foreign reference
> >> style 'trackartist' vs 'artistid'?
> >>
> >> Thanks,
> >> Ken
> >>
> >>
> >> On 01/08/2017 04:47 AM, Kees Nuyt wrote:
> >>> On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner
> >>>  wrote:
> >>>
> >>>> Hello SQLusers,
> >>>>
> >>>> The error below occurs even though the
> >>>>
> >>>> CREATE TABLE track(
> >>>>
> &

Re: [sqlite] Foreign key error...

[Ken Wagner]

Keith,

Ahh, better to be specific and avoid simplistic assumptions.

For foreign keys which is better: 'trackerartist' or 'artistid' in the 
track file? Does it matter? Is it personal preference?


Ken


On 01/08/2017 05:46 AM, Keith Medcalf wrote:

... join ... using (column) has nothing whatever to do with foreign keys.

"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is sweetening and full of 
calories, but has no nutritional value) for the expression "FROM a, b WHERE a.c = b.c"

This is so, for example, if you use really long stupid names it saves 
considerable space and typing:

SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined USING 
(TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
-vs-
SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE 
TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable = 
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 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 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 informative?

I.e., wherever it is seen it shows the track-artist link? But is more
demanding when coding:

  'on trackerartist = artistid' vs 'using (artistid)'

Best or preferred SQLite3 practice for using which foreign reference
style 'trackartist' vs 'artistid'?

Thanks,
Ken


On 01/08/2017 04:47 AM, Kees Nuyt wrote:

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.org/foreignkeys.html was observed.

It appears that 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
bbd85d235f7037c6a033a9690534391ffeacecc8

sqlite> select artistname, trackname from artist inner join track using
(trackartist);
Error:\ cannot join using column trackartist - column not present in
both tables
sqlite> .tables track
track
sqlite> .schema track
CREATE TABLE track(
trackid INTEGER,
trackname   TEXT,
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
sqlite> .schema artist
CREATE TABLE artist(
artistidINTEGER PRIMARY KEY,
artistname  TEXT

Am I missing something important here?

The error message is quite informative: the artist table does
not have a column trackartist.

Try:
select artistname, trackname from artist inner join track on
trackartist = artistid;

HTH


___
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

Re: [sqlite] Foreign key error...

[Keith Medcalf]

... join ... using (column) has nothing whatever to do with foreign keys.  

"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is sweetening 
and full of calories, but has no nutritional value) for the expression "FROM a, 
b WHERE a.c = b.c"

This is so, for example, if you use really long stupid names it saves 
considerable space and typing:

SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined USING 
(TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
-vs-
SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE 
TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable = 
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 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 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 informative?
> 
> I.e., wherever it is seen it shows the track-artist link? But is more
> demanding when coding:
> 
>  'on trackerartist = artistid' vs 'using (artistid)'
> 
> Best or preferred SQLite3 practice for using which foreign reference
> style 'trackartist' vs 'artistid'?
> 
> Thanks,
> Ken
> 
> 
> On 01/08/2017 04:47 AM, Kees Nuyt wrote:
> > 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.org/foreignkeys.html was observed.
> >>
> >> It appears that 'trackerartist' should be named 'artistid'.
> >>
> >> SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
> >> bbd85d235f7037c6a033a9690534391ffeacecc8
> >>
> >> sqlite> select artistname, trackname from artist inner join track using
> >> (trackartist);
> >> Error:\ cannot join using column trackartist - column not present in
> >> both tables
> >> sqlite> .tables track
> >> track
> >> sqlite> .schema track
> >> CREATE TABLE track(
> >>trackid INTEGER,
> >>trackname   TEXT,
> >>trackartist INTEGER,
> >>FOREIGN KEY(trackartist) REFERENCES artist(artistid)
> >> );
> >> sqlite> .schema artist
> >> CREATE TABLE artist(
> >>artistidINTEGER PRIMARY KEY,
> >>artistname  TEXT
> >>
> >> Am I missing something important here?
> > The error message is quite informative: the artist table does
> > not have a column trackartist.
> >
> > Try:
> > select artistname, trackname from artist inner join track on
> > trackartist = artistid;
> >
> > HTH
> >
> 
> ___
> 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

Re: [sqlite] Foreign key error...

[Ken Wagner]

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


I.e., wherever it is seen it shows the track-artist link? But is more 
demanding when coding:


'on trackerartist = artistid' vs 'using (artistid)'

Best or preferred SQLite3 practice for using which foreign reference 
style 'trackartist' vs 'artistid'?


Thanks,
Ken


On 01/08/2017 04:47 AM, Kees Nuyt wrote:

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.org/foreignkeys.html was observed.

It appears that 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
bbd85d235f7037c6a033a9690534391ffeacecc8

sqlite> select artistname, trackname from artist inner join track using
(trackartist);
Error:\ cannot join using column trackartist - column not present in
both tables
sqlite> .tables track
track
sqlite> .schema track
CREATE TABLE track(
   trackid INTEGER,
   trackname   TEXT,
   trackartist INTEGER,
   FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
sqlite> .schema artist
CREATE TABLE artist(
   artistidINTEGER PRIMARY KEY,
   artistname  TEXT

Am I missing something important here?

The error message is quite informative: the artist table does
not have a column trackartist.

Try:
select artistname, trackname from artist inner join track on
trackartist = artistid;

HTH



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

[Kees Nuyt]

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.org/foreignkeys.html was observed.
>
> It appears that 'trackerartist' should be named 'artistid'.
>
> SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37 
> bbd85d235f7037c6a033a9690534391ffeacecc8
>
> sqlite> select artistname, trackname from artist inner join track using 
> (trackartist);
> Error:\ cannot join using column trackartist - column not present in 
> both tables
> sqlite> .tables track
> track
> sqlite> .schema track
> CREATE TABLE track(
>   trackid INTEGER,
>   trackname   TEXT,
>   trackartist INTEGER,
>   FOREIGN KEY(trackartist) REFERENCES artist(artistid)
> );
> sqlite> .schema artist
> CREATE TABLE artist(
>   artistidINTEGER PRIMARY KEY,
>   artistname  TEXT
>
> Am I missing something important here?

The error message is quite informative: the artist table does
not have a column trackartist.

Try:
select artistname, trackname from artist inner join track on
trackartist = artistid;

HTH

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key error...

[Ken Wagner]

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 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37 
bbd85d235f7037c6a033a9690534391ffeacecc8


sqlite> select artistname, trackname from artist inner join track using 
(trackartist);
Error:\ cannot join using column trackartist - column not present in 
both tables

sqlite> .tables track
track
sqlite> .schema track
CREATE TABLE track(
  trackid INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
sqlite> .schema artist
CREATE TABLE artist(
  artistidINTEGER PRIMARY KEY,
  artistname  TEXT

Am I missing something important here?

Thanks,

Ken


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key integrity checking.

[Dominique Devienne]

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 enforcement is off unless a compile option is used.
> -So unless you have full control over what's accessing a database then you
> can't be sure clients are enforcing it.
> -There's a foreign_keys_check pragma which will run an integrity check and
> give you all the key violations.
> -"deferrable initially deferred" is the only way to have a deferred key,
> any other combo will be immediate. You can't change the schema for that at
> will.
> -You can force everything to be deferred if you use the defer_foreign_keys
> pragma, but you can't force anything to immediate if it started deferred.
> -Violation messages will often not tell you exactly which foreign key it
> was that was broken, though if you name your constraints it can help.
> -In keeping track of deferred foreign keys basically a tally of how many
> keys were broken vs fixed is kept. If things don't come out even it can't
> tell you where the problem record was, only that there was at least one
> problem somewhere.
> -A unique index has to be kept on the target (parent) field(s). Optionally
> a regular one on the source (child) fields speeds up enforcement checks.


Great summary David!

I'd just add the new recently announced ".lint fkey-indexes"
from the shell tool (cf http://sqlite.org/releaselog/3_16_2.html),
which find instances of un-indexed FK child column(s).

This is a classic in Oracle, where such unindexed FKs can easily lead to
deadlocks.
In SQLite, it's "just" of matter of performance, since locking is always at
the db level anyway. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key integrity checking.

[David Raymond]

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 enforcement is off unless a compile option is used.
-So unless you have full control over what's accessing a database then you 
can't be sure clients are enforcing it.
-There's a foreign_keys_check pragma which will run an integrity check and give 
you all the key violations.
-"deferrable initially deferred" is the only way to have a deferred key, any 
other combo will be immediate. You can't change the schema for that at will.
-You can force everything to be deferred if you use the defer_foreign_keys 
pragma, but you can't force anything to immediate if it started deferred.
-Violation messages will often not tell you exactly which foreign key it was 
that was broken, though if you name your constraints it can help.
-In keeping track of deferred foreign keys basically a tally of how many keys 
were broken vs fixed is kept. If things don't come out even it can't tell you 
where the problem record was, only that there was at least one problem 
somewhere.
-A unique index has to be kept on the target (parent) field(s). Optionally a 
regular one on the source (child) fields speeds up enforcement checks.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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 things such as ensuring foreign key integrity and checking for it.

Thanks for everyone's time.

-- 
   << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
   Non Impediti Ratione Cogatationis
   Women and cats will do as they please, and men and dogs
should relax and get used to the idea. -Robert A. Heinlein

___
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] Foreign key integrity checking.

[Michael Tiernan]

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.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[David Raymond]

For the specific case below I was just banging something out to test if it 
worked, so didn't adhere to the best design practices. All your comments are 
good ones for actual designs though.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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
>...> (
>...>   id integer primary key,
>...>   a int not null,
>...>   b int not null,
>...>   otherStuff,
>...>   unique (a, b)
>...> );
>
> sqlite> create table childTable
>...> (
>...>   id integer primary key,
>...>   a int, --nullable
>...>   b int, --nullable
>...>   otherStuff,
>...>   foreign key (a, b) references parentTable (a, b)
>...> );
>

But why would you do that?

If you have unique(a, b), that implies it's your natural key (NK),
and id is "just" the surrogate key (SK). And the whole point of
a surrogate key is to have lighter-weight (to store and index)
and faster to match against foreigns keys (FKs). Otherwise you
might as well set (a, b) as your PK, and not have an SK at all. --DD
___
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

Re: [sqlite] Foreign key child index question

[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> create table childTable
>...> (
>...>   id integer primary key,
>...>   a int, --nullable
>...>   b int, --nullable
>...>   otherStuff,
>...>   foreign key (a, b) references parentTable (a, b)
>...> );
>

But why would you do that?

If you have unique(a, b), that implies it's your natural key (NK),
and id is "just" the surrogate key (SK). And the whole point of
a surrogate key is to have lighter-weight (to store and index)
and faster to match against foreigns keys (FKs). Otherwise you
might as well set (a, b) as your PK, and not have an SK at all. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[David Raymond]

Hurray for intelligent logic! Also seems to work for composite foreign keys. 
Thank you all for checking more into this. And thank you to the developers for 
already having this in there. I think this would be worthy of having a couple 
sentences written up for the "Foreign Key Support" page.
http://www.sqlite.org/foreignkeys.html#fk_indexes


SQLite version 3.15.2 2016-11-28 19:13:37
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 = on;

sqlite> create table parentTable
   ...> (
   ...>   id integer primary key,
   ...>   a int not null,
   ...>   b int not null,
   ...>   otherStuff,
   ...>   unique (a, b)
   ...> );

sqlite> create table childTable
   ...> (
   ...>   id integer primary key,
   ...>   a int, --nullable
   ...>   b int, --nullable
   ...>   otherStuff,
   ...>   foreign key (a, b) references parentTable (a, b)
   ...> );

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SCAN TABLE childTable

sqlite> create index fullIndex on childTable (a, b);

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX fullIndex (a=? AND b=?)

sqlite> create index halfIndex on childTable (a, b) where b is not null;

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX halfIndex (a=? AND b=?)

sqlite> create index partialIndex on childTable (a, b) where a is not null and 
b is not null;

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX partialIndex (a=? AND b=?)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf 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, 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, and using an index which left out
>> the NULL key values would lead to the right results.  Should be possible to
>> use that for an optimization case.  I suppose it might lead to quite a
>> saving in filespace for some tables.
>>
>
> Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
> the example? Or just that it would be possible as an enhancement request?

Indexes qualified with "WHERE col IS NOT NULL" on child tables can be 
used for FK processing. Simon is right that you can use EXPLAIN QUERY 
PLAN to see this. Without the index, EQP says "SCAN TABLE childTable". 
After it is created, "SEARCH TABLE childTable USING ... INDEX ...".

   sqlite> create table parentTable( id integer primary key, restOfFields);
   sqlite> create table childTable (
  ...>   id integer primary key,
  ...>   fkField int references parentTable, --usually null
  ...>   restOfFields --includes many more foreign keys
  ...> );
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   sqlite> PRAGMA foreign_keys = 1;
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   0|0|0|SCAN TABLE childTable
   sqlite> create index alternateChildIndex1 on childTable (fkField) 
where fkField is not null;
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   0|0|0|SEARCH TABLE childTable USING COVERING INDEX 
alternateChildIndex1 (fkField=?)

Dan.

___
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

Re: [sqlite] Foreign key child index question

[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 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, and using an index which left out
the NULL key values would lead to the right results.  Should be possible to
use that for an optimization case.  I suppose it might lead to quite a
saving in filespace for some tables.



Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?


Indexes qualified with "WHERE col IS NOT NULL" on child tables can be 
used for FK processing. Simon is right that you can use EXPLAIN QUERY 
PLAN to see this. Without the index, EQP says "SCAN TABLE childTable". 
After it is created, "SEARCH TABLE childTable USING ... INDEX ...".


  sqlite> create table parentTable( id integer primary key, restOfFields);
  sqlite> create table childTable (
 ...>   id integer primary key,
 ...>   fkField int references parentTable, --usually null
 ...>   restOfFields --includes many more foreign keys
 ...> );
  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  sqlite> PRAGMA foreign_keys = 1;
  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  0|0|0|SCAN TABLE childTable
  sqlite> create index alternateChildIndex1 on childTable (fkField) 
where fkField is not null;

  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  0|0|0|SEARCH TABLE childTable USING COVERING INDEX 
alternateChildIndex1 (fkField=?)


Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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 chosen to 
do for your case.  Prefix your SELECT command with EXPLAIN QUERY PLAN:



It’s probably easiest to do this using the SQLite command-line tool.  The plan 
you get back will describe which indexes SQLite has decided to use, or whether 
it couldn’t find a useful index and had to scan the table instead.

Note that using ANALYZE after you have created your indexes can cause a 
dramatic improvement in the options SQLite picks when constructing query plans. 
 If you are in a position of worrying about optimization it’s useful to 
understand ANALYZE and EXPLAIN QUERY PLAN.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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
> > means that it does not have a parent.
>
> You are correct.  I missed that.
>
> So yes, the original poster was correct, and using an index which left out
> the NULL key values would lead to the right results.  Should be possible to
> use that for an optimization case.  I suppose it might lead to quite a
> saving in filespace for some tables.
>


Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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 original poster was correct, and using an index which left out the 
NULL key values would lead to the right results.  Should be possible to use 
that for an optimization case.  I suppose it might lead to quite a saving in 
filespace for some tables.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

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

"All foreign key constraints in SQLite are handled as if MATCH SIMPLE were
specified."

"If "MATCH SIMPLE" is specified, then a child key is not required to
correspond to any row of the parent table if one or more of the child key
values are NULL."

Therefore SQLite would *not *need to find a parent for that row.

So i guess i am still hoping that, if possible, SQLite could be enhanced as
David's question was getting at. :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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 field(s) are defined as NOT NULL, you have a good 
suggestion.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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 majority of
> the time are null. I've currently got indexes on the child fields for the
> purposes of speeding up the foreign key checks, but what I'm wondering is
> if I used a conditional index which has "where fkField is not null", will
> that index be usable by the internal foreign key checker?
>
> Sorry, but the answer is no.  SQLite needs to be able to find the rows
> where the key-value is NULL.  It can’t do that from an index which doesn’t
> include those rows.
>
>
Why must SQLite find rows where the value is NULL? To me, this sounded like
a good enhancement request if it's not already the case. Am i missing
something?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[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 for the 
> purposes of speeding up the foreign key checks, but what I'm wondering is if 
> I used a conditional index which has "where fkField is not null", will that 
> index be usable by the internal foreign key checker?

Sorry, but the answer is no.  SQLite needs to be able to find the rows where 
the key-value is NULL.  It can’t do that from an index which doesn’t include 
those rows.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key child index question

[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 up the foreign key checks, but what I'm wondering is if I used a 
conditional index which has "where fkField is not null", will that index be 
usable by the internal foreign key checker? I'd like to avoid having 10 indexes 
each of which is 90% full of nulls for example.

create table parentTable
(
  id integer primary key,
  restOfFields
);

create table childTable
(
  id integer primary key,
  fkField int references parentTable, --usually null
  restOfFields --includes many more foreign keys
);

create index childIndex1 on childTable (fkField);--normal fk child index

create index alternateChildIndex1 on childTable (fkField) where fkField is not 
null;
--will this be used, and help cut down on wasted space?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FOREIGN KEY constraint failed

[James K. Lowden]

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.  

> We might be - though I assume the OP implicated only deferred 
> constraints - since immediate constraints will fail on contact, and
> as such, no mystery surrounds their origins.

One table may have more than one constraint.  Primary key violations
are reported as such, but CHECK constraint and FK constraint messages
don't mention the column involved.  

OP: I investigated the problem a year ago or more and concluded it's not
easy to remedy.  Today violations are simply counted.  To report them
in detail would require carrying much more state, and externalizing
constraint conditions in human-readable form.  Unless you have a patch
that does all that, I doubt PRAGMA DEBUG_MODE will be realized.  

--jkl

[sqlite] FOREIGN KEY constraint failed

[no...@null.net]

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 deferred
> constraints - since immediate constraints will fail on contact, and
> as such, no mystery surrounds their origins.  My assumption might be
> wrong.

There is plenty of mystery around immediate constraints when triggers
are involved. I often have an issue with statements failing with the
generic foreign key message where the actual issue is three or four
trigger levels deep. So I can only add my +1 to the "this is an issue"
position as well as a +1 to "please can we have *some* kind of help."

What I usually end up doing is re-executing the statement with foreign
keys turned off, and then run the foreign_key_check pragma. But it
doesn't always work because if the problem trigger/statement doesn't
fail then later statements sometimes mask the original problem.

So my suggestion would be a development pragma to request that SQLite
does this itself before the transaction gets rolled back, adding the
results of the foreign_key_check to the error message.

Mark
-- 
Mark Lawrence

[sqlite] FOREIGN KEY constraint failed

[R Smith]

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 contact, and as 
such, no mystery surrounds their origins.
My assumption might be wrong.

[sqlite] FOREIGN KEY constraint failed

[Keith Medcalf]

Are we confusing immediate constraints (checked per statement) with DEFERRED 
constraints (checked at COMMIT time) again?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> 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 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 of course sqlite knows which table/field failed to flag the error,
> it
> > doesn't throw a dice to do it.
> 
> The thing you are missing, is that there might be thousands of FK
> violations throughout a transaction, all of which (or most of which)
> might get resolved before the end of the transaction, and as such is
> absolutely useless to inspect/record/notify/whatever.
> 
> Let's assume there are one thousand violations, and three of them did
> not get resolved, such as violation number 322, no. 567 and no. 828.
> If you "ask the user" or the program via API about every one of the 1000
> violations, surely the time-waste will be intense, and even if you can
> live with the time-waste, how will the application/user ever know that
> violation no. 435, for instance, is going to definitely get resolved so
> that it might report back to the API some form of "OK, we can accept
> this one" or record for its own purposes the violation to "deal with
> later" when in fact at some point it gets resolved without necessarily a
> second check and certainly not a second failure to revisit it?
> 
> It is extremely rare that the "Last violation" (number 1000 in our
> example above) is going to end up being THE ONE, or even "one of" the
> offenders.  Perhaps only in cases where there is only 1 FK violation in
> the entire scope of the transaction, and those cases are rarest (such as
> a single insert/delete) and if it does happen, you already know the
> exact item causing violation, so the API to disclose its identity is
> superfluous.
> 
> The only way this feature is feasible is keeping a complete list of
> violations internally and a mechanism to revisit them marking the
> resolve (if any). The mechanism itself will be heavy and the memory
> footprint of the list can run into gigabytes easily, even for a mediocre
> database, seeing as a transaction updating 10 rows may easily need to
> check hundreds of FK constraints and recursed constraints.
> 
> As Mr. Bee pointed out - we see this question asked often, lots of
> people would like to have it implemented. This may be true, but that's
> simply because, without investigation, the concept/implementation seems
> easy to lots of people. It only seems that way though.
> 
> Cheers,
> Ryan
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thank you for reply !  

Yes you are right in most of your points.  

The code overhead/footprint can be inside preprocessor macros and we can have
for development as we normally do a build with debug ON so in production
there will be nothing new.  

Throwing a bunch of false positives to stderr/sqlite3_(hook*) in deep debug
mode can be managed by a user script that go through the output and filter
then or do other things each use case my require.  

For example when I asked this question again I've got this error in a
"delete" statement with one specific id but that exploded to several possible
tables/fields combination and it took me 3 hours to find it. As usual it was
silly once you discover it but till then you loose some hairs.  


Let's have a compile time debug mode in sqlite where things like this and
others can make possible find alternative ways to solve everyday 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 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 of course sqlite knows which table/field failed to flag the error, it
>> doesn't throw a dice to do it.
>> 

>  The thing you are missing, is that there might be thousands of FK 
> violations throughout a transaction, all of which (or most of which) 
> might get resolved before the end of the transaction, and as such is 
> absolutely useless to inspect/record/notify/whatever.
> 
> Let's assume there are one thousand violations, and three of them did 
> not get resolved, such as violation number 322, no. 567 and no. 828.
> If you "ask the user" or the program via API about every one of the 1000 
> violations, surely the time-waste will be intense, and even if you can 
> live with the time-waste, how will the application/user ever know that 
> violation no. 435, for instance, is going to definitely get resolved so 
> that it might report back to the API some form of "OK, we can accept 
> this one" or record for its own purposes the violation to "deal with 
> later" when in fact at some point it gets resolved without necessarily a 
> second check and certainly not a second failure to revisit it?
> 
> It is extremely rare that the "Last violation" (number 1000 in our 
> example above) is going to end up being THE ONE, or even "one of" the 
> offenders. Perhaps only in cases where there is only 1 FK violation in 
> the entire scope of the transaction, and those cases are rarest (such as 
> a single insert/delete) and if it does happen, you already know the 
> exact item causing violation, so the API to disclose its identity is 
> superfluous.
> 
> The only way this feature is feasible is keeping a complete list of 
> violations internally and a mechanism to revisit them marking the 
> resolve (if any). The mechanism itself will be heavy and the memory 
> footprint of the list can run into gigabytes easily, even for a mediocre 
> database, seeing as a transaction updating 10 rows may easily need to 
> check hundreds of FK constraints and recursed constraints.
> 
> As Mr. Bee pointed out - we see this question asked often, lots of 
> people would like to have it implemented. This may be true, but that's 
> simply because, without investigation, the concept/implementation seems 
> easy to lots of people. It only seems that way though.
> 
> Cheers,
> Ryan
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[R Smith]

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 of course sqlite knows which table/field failed to flag the error, it
> doesn't throw a dice to do it.

The thing you are missing, is that there might be thousands of FK 
violations throughout a transaction, all of which (or most of which) 
might get resolved before the end of the transaction, and as such is 
absolutely useless to inspect/record/notify/whatever.

Let's assume there are one thousand violations, and three of them did 
not get resolved, such as violation number 322, no. 567 and no. 828.
If you "ask the user" or the program via API about every one of the 1000 
violations, surely the time-waste will be intense, and even if you can 
live with the time-waste, how will the application/user ever know that 
violation no. 435, for instance, is going to definitely get resolved so 
that it might report back to the API some form of "OK, we can accept 
this one" or record for its own purposes the violation to "deal with 
later" when in fact at some point it gets resolved without necessarily a 
second check and certainly not a second failure to revisit it?

It is extremely rare that the "Last violation" (number 1000 in our 
example above) is going to end up being THE ONE, or even "one of" the 
offenders.  Perhaps only in cases where there is only 1 FK violation in 
the entire scope of the transaction, and those cases are rarest (such as 
a single insert/delete) and if it does happen, you already know the 
exact item causing violation, so the API to disclose its identity is 
superfluous.

The only way this feature is feasible is keeping a complete list of 
violations internally and a mechanism to revisit them marking the 
resolve (if any). The mechanism itself will be heavy and the memory 
footprint of the list can run into gigabytes easily, even for a mediocre 
database, seeing as a transaction updating 10 rows may easily need to 
check hundreds of FK constraints and recursed constraints.

As Mr. Bee pointed out - we see this question asked often, lots of 
people would like to have it implemented. This may be true, but that's 
simply because, without investigation, the concept/implementation seems 
easy to lots of people. It only seems that way though.

Cheers,
Ryan

[sqlite] FOREIGN KEY constraint failed

[Klaas Van B.]

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 foreign_keys = OFF;

After restructuring is executed successfully you can test the result by 
querying with this pragma ON.
http://sqlite.org/pragma.html#pragma_foreign_keys

Kind regards/Vriendelijke groeten.
Klaas `Z4us` Van B., CEO/CIO LI#437429414

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

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 of course sqlite knows which table/field failed to 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, 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 that obscure message.
>Sigh.
> 
> Oh well? 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thanks for reply !  

I also do it using the sqlite3_trace hook but when you have a database with a
bunch of foreign keys it would be a lot easier/faster if sqlite could tell us
exactly where it was (sqlite must know it to flag the error).  

I already found the problem but it took 3 hours to find.  

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 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 have a message like this:
>> 

>  You can write your own wrapper to do this. I have 850 places in my app
>where I do query or exec, so in the event of failure I need to know which one
>and what the sql was. When a problem occurs I log this information.
> 
> --
> Cheers -- Tim
> 
>   (, 0 bytes) [View| Download]
>  ?
>
>  
>
>  
>
>  



?

-- next part --
An embedded and charset-unspecified text was scrubbed...
Name: 
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160404/b120d769/attachment.ksh>

[sqlite] FOREIGN KEY constraint failed

[Petite Abeille]

> 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 that obscure message. Sigh.

Oh well? 

[sqlite] FOREIGN KEY constraint failed

[Tim Streater]

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 have a message like this:

You can write your own wrapper to do this. I have 850 places in my app where I 
do query or exec, so in the event of failure I need to know which one and what 
the sql was. When a problem occurs I log this information.

--
Cheers  --  Tim

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

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 with a small memory footprint (Unless
> you talk about the Vic20 as being the smallest footprint I've used, but not
> relevant to SQLite, and my skill was writing text based paint program),
> but, throwing results like this into memory could cause more issues for
> devices that have KILOBYTES worth of memory to play with as a whole, versus
> a machine with Gigabytes of extra-never-been-used-in-its-lifespan memory.
> 
> If maybe there were some kind of interface that the library could expose so
> that if a constraint error occurs, something external to SQLite can deal
> with it, regardless of what the results are. If the developer wants to
> know what the constraint faults are, they just map their own routines to
> whatever interface SQLite could provide and the SQLite engine can just feed
> whatever it can. The owness of whatever that output is belongs to the
> developer, not to the SQLite dev team, and reservations on what the output
> of this new routine is apt to change at any time. If the events triggered
> by the constraint failures are invalid at the start, but then become valid
> later on, it'd be again up to the developer to determine what happened, not
> the SQLite dev team.
> 
> My two cents.
> 
> 
> On Mon, Apr 4, 2016 at 1:49 PM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
> 
>  
>>Thank you for reply !
>> 
>> Good point, but for sure there is great minds here that can come with a
>> solution to this problem !
>> 
>> Sqlite could use a table in memory (or a hash table) and add delete from
>>it
>> as it found violations/resolve then and at then end it has all the
>> violations
>> to show, it can 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
>>> 
>>> Domingo Alvarez Duarte wrote:
>>> 
>>>  
>>>>But even then could it have let's say a place for record the last foreign
>>>> key
>>>> violation ?
>>>> 
>>>> 

>>>  This would not help if that last constraint is no longer violated at the
>>> end of the transaction.
>>> 
>>> 
>>> Regards,
>>> Clemens
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 

>>  ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 

>  ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

I think I found a solution to it !  

If the cost of it is too much for normal operation we could have a "pragma
debug_foreign_key" (or debug everything) and turn it on, then run again the
failed statement and in this mode extra code that saves all info for a
detailed error message 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
> solution to this problem ! 
> 
> Sqlite could use a table in memory (or a hash table) and add delete from it
> as it found violations/resolve then and at then end it has all the
>violations
> to show, it can 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
>> 
>> Domingo Alvarez Duarte wrote:
>> 
>>  
>>>But even then could it have let's say a place for record the last foreign
>>> key
>>> violation ?
>>> 
>>> 

>>  This would not help if that last constraint is no longer violated at the
>> end of the transaction.
>> 
>> 
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> 
>> 
>> 
>> 

>  ?
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thank you for reply !  

Good point, but for sure there is great minds here that can come with a
solution to this problem !  

Sqlite could use a table in memory (or a hash table) and add delete from it
as it found violations/resolve then and at then end it has all the violations
to show, it can 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
>
>  Domingo Alvarez Duarte wrote:
>  
>>But even then could it have let's say a place for record the last foreign
>>key
>> violation ?
>> 

>  This would not help if that last constraint is no longer violated at the
> end of the transaction.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?