I’m running the following SQLite workaround to add a primary key to a table that did not have one. I am getting a datatype mismatch on
INSERT INTO cities SELECT id, name FROM old_cities;
However, the fields have exactly the same type. Is it possible that his happens due to running the queries from DbBrowser for SQLite?
CREATE table cities ( id INTEGER NOT NULL, name TEXT NOT NULL ); INSERT INTO cities (id, name) VALUES ('pan', 'doul'); END TRANSACTION; PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE cities RENAME TO old_cities; --CREATE TABLE cities ( -- id INTEGER NOT NULL PRIMARY KEY, -- name TEXT NOT NULL --); CREATE TABLE cities ( id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id) ); SELECT * FROM old_cities; INSERT INTO cities SELECT id, name FROM old_cities; DROP TABLE old_cities; COMMIT;
Advertisement
Answer
You have defined the column id
of the table cities
to be INTEGER
, but with this:
INSERT INTO cities (id, name) VALUES ('pan', 'doul');
you insert the string 'pan'
as id
.
SQLite does not do any type checking in this case and allows it.
Did you mean to insert 2 rows each having the names 'pan'
and 'doul'
?
If so, you should do something like:
INSERT INTO cities (id, name) VALUES (1, 'pan'), (2, 'doul');
Later you rename the table cities
to old_cities
and you recreate cities
but you do something different: you define id
as INTEGER
and PRIMARY KEY
.
This definition is the only one that forces type checking in SQLite.
So, when you try to insert the rows from old_cities
to cities
you get an error because 'pan'
is not allowed in the column id
as it is defined now.