Unsatisfied foreign keys when populating a table

Problems, tips, hints - all about usage.

Unsatisfied foreign keys when populating a table

Postby stefano.lodi » Thu Apr 27, 2017 11:28 pm

When I populate a table with a referential constraint I get foreign key mismatches I fail to understand.

For example:

CREATE TABLE test (
A CHAR PRIMARY KEY
);

<populate test with 10 rows 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'l'>

CREATE TABLE test2 (
B CHAR REFERENCES test (A)
PRIMARY KEY
);

select *
from test;

a
b
c
d
e
f
g
h
i
l

I can insert 'a' into test2 using "Edit the table" | Grid view | Ins, and commit. However, populating test2 returns "Error while populating table: FOREIGN KEY constraint failed". For example, using the dictionary plugin, as a dictionary I used a text file containing

a b c d e f g h i l

and, using the constant plugin, as a constant I used 'a' (without quotes).
stefano.lodi
 
Posts: 1
Joined: Thu Apr 27, 2017 10:09 pm

Re: Unsatisfied foreign keys when populating a table

Postby Googie » Fri Apr 28, 2017 2:32 pm

Here's a thing:

Dictionary plugin splits lines using Unix new-line character, leaving \r in the end of the used value. This makes the key mistmatching the referenced column (it does not have this extra leading \r in the end).
It's a bug in dictionary plugin and I reported it to tracki it: https://bugs.sqlitestudio.pl/index.rvt?id=3310

Now, what you can do is to put all letters in one line, just separate them with whitespace - this way there are no new lines involved and everything works fine.
In case you need to separate values with new lines, you will need to use some file editor which lets you choose what line-ending you want to use (MacOS/Unix/Windows) and pick Unix to make it work.

Sorry for the inconvenience. It will eventually be fixed.
Googie
Site Admin
 
Posts: 790
Joined: Wed Jul 27, 2011 8:04 pm


Return to Usage

Who is online

Users browsing this forum: No registered users and 2 guests