CODEWITHSUNDEEP
c++sqlsqlite
timofey.tatarinov
timofey.tatarinov

Reputation: 127

SQLite INSERT command return error "column number is not unique"

I have a text file with rows (lines). Each row is a record in database table. I read this file and fill database.

Tables creating command:

CREATE TABLE gosts(number TEXT PRIMARY KEY, userNumber TEXT, status TEXT, date TEXT, title TEXT, engTitle TEXT, description TEXT, mainCategory INTEGER, category INTEGER, subCategory INTEGER);

Inserting query:

INSERT INTO gosts VALUES ("30331.8-95", "ÃÎÑÒ 30331.8-95", "Äåéñòâóþùèé", "01.07.1996", "Ýëåêòðîóñòàíîâêè çäàíèé. ×àñòü 4. Òðåáîâàíèÿ ïî îáåñïå÷åíèþ áåçîïàñíîñòè. Îáùèå òðåáîâàíèÿ ïî ïðèìåíåíèþ ìåð çàùèòû äëÿ îáåñïå÷åíèÿ áåçîïàñíîñòè. Òðåáîâàíèÿ ïî ïðèìåíåíèþ ìåð çàùèòû îò ïîðàæåíèÿ ýëåêòðè÷åñêèì òîêîì", "Electrical installations of buildings. Part 4. Protection for safety. Applisation of protective measues for safety. Measures of protection against electric shock", "Íàñòîÿùèé ñòàíäàðò óñòàíàâëèâàåò îáùèå òðåáîâàíèÿ ïî ïðèìåíåíèþ ìåð çàùèòû äëÿ îáåñïå÷åíèÿ áåçîïàñíîñòè è òðåáîâàíèÿ ïî ïðèìåíåíèþ ìåð çàùèòû îò ïîðàæåíèÿ ýëåêòðè÷åñêèì òîêîì ïðè ýêñïëóàòàöèè ýëåêòðîóñòàíîâîê çäàíèé", 37, 333, 628);

Please ignore encoding problems. Source file has cp1251 encoding, but inserting sample is taken from console. I tried to use utf-8 but had the same problem.

SQLite using code above:

if(sqlite3_prepare_v2(database, query, -1, &statement, 0) == SQLITE_OK) {
...
}

Function calling doesn't return SQLITE_OK. And I gea error message by:

string error = sqlite3_errmsg(database);
if(error != "not an error") cout << query << " " << error << endl;

Strangely, some records are inserted without error and I can't find differences between good and bad records.

I can provide more information if needed.

Upvotes: 1

Views: 6754

Answers (1)

Gibron
Gibron

Reputation: 1369

I would bet that the difference between the good and bad rows were whether or not the value associated with the 'number' column was already in the table.

This is one of the reasons that table designs usually do not use TEXT valued columns for PRIMARY KEYs.

If it is possible to re-create your table, I would create an ID field responsible for being the PRIMARY KEY for your table. Further enable the IDENTITY property for auto increment of your primary key value.

This should prevent insertion failure due to having duplicate values in the 'number' column.

Now if values in 'number' column must be unique then you should add a UNIQUE constraint on that column.
NOTE: The UNIQUE will yield the same error you are currently receiving as it appears you are trying to add multiple rows with the same value for column'number'

Review the SQLite CREATE TABLE documentation for more details.

Upvotes: 2

Related Questions