Keith Medcalf, thank you very much for detailed response. So, my python script is working as intended. This also explain, why my testing database works when I set UNIQUE to one key. All makes sense now. From now on, whenever I will create new database, I will make sure I will have UNIQUE set if I will want to prevent duplicate entries.
I have question about creating unique index. Can I create it on already existing batabase/table?
I tried your command several times, through Python and directly inside SQLIte environment (never before I have ran SQLite command on it's own, so I probably made a mistake. I will show you what I did:
I python I ran these commands, I tried several different variations what you suggested:
import sqlite3
conn = sqlite3.connect('./test3.db')
c = conn.cursor()
c.execute(create unique index table1.timestamp on table1(timestamp))
# c.execute(create unique index table1timestamp on table1(timestamp))
# c.execute(create unique index table1(timestamp) on table1(timestamp))
# c.execute(CREATE UNIQUE index table1 timestamp on table1(timestamp))
I always get syntax error.
Then I tried directly inside SQLite3 from the terminal. Commands were:
sqlite3
sqlite> .open test3.db
sqlite> create unique index table1.timestamp on table1(timestamp)
...> .close test3.db
test3.db is mock database with same schema I mentioned in first post with one record already inserted, to test if I can create unique index). I didn't get any error running upper SQLite commands, but table didn't change, there is no UNIQUE property for timestamp and there is no collusion, same data is being added, which tells me, database and table are the same as before i ran upper commands.
I'm completely noob, beginner in Python, first time dealing with database...
Is there a simple way, preferably in Python command, that I can convert existing database/table into UNIQUE? I would like to use existing database file if possible. By creating new one, I would lose all data already inserted.