[SQLite] NOT NULL Constraint failed

Hi all,

I believe I recall that a NOT NULL created TABLE is not involved by a non save data in it.

In an Import Data from XML Method, I get a Nil when my data field is empty. So, I added a Nil checking. Then, I continue to get this error at the first empty field.

The first code had no Nil test and I get an error (because of a Nil).
The second code was a simple If …/… End If
The third code is as below because when the If line is Nil, I HAVE to store something in Target_Record (who is a DataBaseRecord).

Used code:

If XML_Record_Row_Node.Child(1).Child(0) <> Nil Then Target_Record.Column("Nom") = XML_Record_Row_Node.Child(1).Child(0).Value Else // Else added to avoid the NOT NULL Constraint failed reported error Target_Record.Column("Nom") = "" End If

Did I recall correctly or did the person’s advice was… not 100% true.

Nota: Usually, I used this kind of code to populate a Record

db.SQLExecute("BEGIN TRANSACTION") db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES "_ +"('Dr.Strangelove','Advisor',1962)")

Code extract comes from the LR (SQLiteDatabase) last code example (page bottom).

“” and Null are not the same thing
if table is created with a NOT NULL constraint, then those fields MUST have a value that is not null

table is created with a NOT NULL constraint so:
“” is not NULL.
Nothing is NULL.

Is this right Dave ?

At last, sometimes the Read code can be far different from the Write code !

that should be correct, yes…

I personally HATE the concept of NULL, but I understand why it needs to exist

So if you input XML defines NULLs and your database cannot accept NULL , then you need a workaround… and what you have is as good as any

I was thinking at adding a space when exporting data to xml if the “field” is empty, then I rejected the idea: I already have the code… and after all I can consider that as a bad idea.

Thank you for your help.

If you don’t want a field to be NULL nor to be an empty string, you can combine a NOT NULL and a CHECK(YourFieldName <> '') constraints in the CREATE TABLE statement.
More info about SQLite constraints here.