On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf <kmedc...@dessus.com> 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.
```
<code>
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[000001888DA2FC6C:*/../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,<NULL>
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,<NULL>
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,<NULL>
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,<NULL>
CREATE TABLE `option4_exception` (`option_exception_id` char(36) NOT NULL
CONSTRAINT `option_exception_idkey` UNIQUE,`apply_from` datetime default
'0000-00-00 00:00:00',`apply_until` datetime default '0000-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,<NULL>
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')]
</code>
```
> 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 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
