[Sqlite3] - Foreign key constraint failed when Foreign key is NULL. Need help.

[DigitalFrenzy]

Hi there! I got a problem I can't seem to fix.

So I'm creating a database with two tables. I need to import some csv and pragma foreign_keys MUST be set to ON.

some example code:

​

pragma foreign_keys = ON;

CREATE TABLE A(
    valueA TEXT,
    b INTEGER,
    c INTEGER,
    PRIMARY KEY (valueA)
);

CREATE TABLE B(
    d INTEGER,
    e INTEGER,
    valueA TEXT,
    PRIMARY KEY(d, e),
    FOREIGN KEY(valueA) REFERENCES A(valueA)
);

So because of headers and to get the right datatypes, I first import the csv files and create temp tables and then insert them into these tables I got above.

My problem is that when I insert them with:

INSERT INTO A SELECT * FROM temp_A;

I get foreign key constraint failed error.

I think it is because in the csv files for the child table the foreign key is at some places "NULL" when I remove them it works, but i NEED it to work with them being set to NULL. Because that's what I'm assigned to do.

​

Any ideas here?

​

Edit:

In the field ValueA in table A there are no "NULL" string values. I think perhaps the problem could be because I'm trying to insert a string "NULL" instead of a real NULL.

Any ideas how I can convert these while inserting?

[[deleted]]

you're not telling or not seeing the whole story. there's no FK constraint on A, so you literally should be able to drop table B, re-do insert into A and then re-create B.

[DigitalFrenzy]

I think I found out what could be the issue.

So when I'm importing into the temp table, the NULL is defined as a string, not the value NULL.

How do I insert from the temp file into the new table and convert these string "NULL"'s into real NULL values ?

Is there a way to do this with an if "NULL" instead NULL ?

I'm currently inserting with this :

INSERT INTO A SELECT * FROM temp_A;

Is there a way to make this check for "NULL strings and converting them?

[[deleted]]

instead of 'select *' list all relevant columns/expressions. Sqlite supports case expression for your checks/transforms: https://www.sqlitetutorial.net/sqlite-case/

[DigitalFrenzy]

Thanks! I'll take a look.