Several unique columns in SQLite

Question

I am trying to create a table where I need it so as NOT to allow rows where the three fields are the same.

When I create a table in Python using SQLLite, I use the following, but I get almost nothing. Usually it stops after recording 2 records, so something, obviously, believes in its duplication.

CREATE TABLE CorpWalletJournal ( date INT, refID INT, refTypeID INT, ownerName1 TEXT, ownerID1 INT, ownerName2 TEXT, ownerID2 INT, argName1 TEXT, argID1 ID, amount INT, balance INT, reason TEXT, accountKey INT, UNIQUE (ownerID1, ownerID2, accountKey, argID1) ); 

So, I would like the database NOT to allow records where ownerID1, ownerID2, accountKey and argID1 are the same.

Can someone help me with this?

Thank you!

Answer 1

I am not sure what the problem is. It works great here:

 import sqlite3 # connect to memory-only database for testing con = sqlite3.connect('') cur = con.cursor() # create the table cur.execute(''' CREATE TABLE CorpWalletJournal ( date INT, refID INT, refTypeID INT, ownerName1 TEXT, ownerID1 INT, ownerName2 TEXT, ownerID2 INT, argName1 TEXT, argID1 ID, amount INT, balance INT, reason TEXT, accountKey INT, UNIQUE (ownerID1, ownerID2, accountKey, argID1) ); ''') con.commit() insert_sql = '''INSERT INTO CorpWalletJournal (date, refID, refTypeID, ownerName1, ownerID1, ownerName2, ownerID2, argName1, argID1, amount, balance, reason, accountKey) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''' ## create 5 rows changing only argID1 - it works: for argid in xrange(5): cur.execute(insert_sql, (1, 1, 1, 'a', 1, 'a', 1, 'a', argid, 1, 1, 'a', 1)) con.commit() # now try to insert a row that is already there: cur.execute(insert_sql, (1, 1, 1, 'a', 1, 'a', 1, 'a', 0, 1, 1, 'a', 1)) 

Error received from last line:

 Traceback (most recent call last): File "teststdio.py", line 41, in <module> cur.execute(insert_sql, (1, 1, 1, 'a', 1, 'a', 1, 'a', 0, 1, 1, 'a', 1)) sqlite3.IntegrityError: columns ownerID1, ownerID2, accountKey, argID1 are not unique 

Answer 2

You are not looking for UNIQUE, but for the PRIMARY KEY. When you set the PRIMARY KEY (ownerID1, ownerID2, accountKey, argID1), then these 4 values ​​together are the row index. This means that if you write a new line with these 4 values ​​equal to the existing one, it will overwrite it. Therefore, each combination of 4 values ​​can exist only once.

UNIQUE, on the other hand, means that each of the 4 values ​​can be used only once.