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 <d3c...@gmail.com> wrote:
>
>
> 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
