Cannot update a field to a NULL value

Databases
1

I am duplicating records in an SQLite database using the following method:

AntennaDB.SQLExecute("CREATE TABLE tmp AS SELECT * FROM RxTx WHERE AntennaID = '" + key + "'")
AntennaDB.SQLExecute("UPDATE tmp SET Index = NULL")
AntennaDB.SQLExecute("INSERT INTO RxTx SELECT * FROM tmp")
AntennaDB.SQLExecute("DROP TABLE tmp")

The table RxTx is in a database which has one other table which references the records in this one. The cross-reference is via the field AntennaID, whose value is stored in the String key.

The idea is to create a new table, tmp, from the matching records in RxTx. The table RxTx has the primary key ‘Index’, and this has the NOT NULL constraint. However, CREATE TABLE does not generate a primary key and nor does it apply the NOT NULL constraint.

I have checked this is the case by examining the table tmp after the first line of this code.

The second line is supposed to replace the existing value of Index with NULL, which should be possible given that there is no NOT NULL constraint.

Then the third line should insert the rows of tmp and the Index should auto-generate.

I have used this approach successfully on the other table in this same database.

However, the second line does not replace the Index field with NULL. There is no constraint-violation error and when I reach the third line there is a non-unique error.

I cannot understand why this does not work (as it does with the other table).

Thanks for any help!

2

Put the constraints and primary key on the create statement. It is up to the database how it creates the table otherwise.

3

After working on this since yesterday afternoon (almost 24 hours ago) I have found a work-around, though I don’t know why it is needed.

I noticed, using DB Browser for SQLite that in the table ‘tmp’ the field name Index is surrounded by double quotes: “Index”, but the other field names are not. So I modified my second line to

AntennaDB.SQLExecute("UPDATE tmp SET ""Index"" = NULL")

And then it worked as expected.

I use this same technique a bit earlier in the same method, to duplicate the record in the other Table. In that one the Primary Key, NOT NULL, field is called AntennaID. When I make the Table ‘tmp’ in that case the equivalent field in that is just AntennaID, not “AntennaID”, and the UPDATE command works without the quotes.

I don’t understand what’s going on here. I have a solution which works but because I don’t understand it I’m not completely happy.

Cheers,
Richard

4

Thanks for the reply – but the thing is I don’t want the NOT NULL constraint otherwise I would not be able to INSERT the records back into the original database unless I changed them to values which are unique. That would a level of bookkeeping complexity that is not necessary if I use NULL and allow auto-generation.

5

Probably because Index is a reserved word in SQLite. Best to avoid those.

2 Likes
6

Ah, yes. Index is a reserved word in just about every SQL. You can get around the issue with the quotes as you have seen. ` also should work as a quote for such things. At least they do in MySQL.

7

OK, that explains it!

Thanks to both of you for responding!