Multiple Unique Columns in SQLite

Question

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

When I create the table in Python using SQLLite, I use the follow, but I hardly get any results at all. It usually stops after writing 2 records, so something is obviously believing its duplicated.

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 to NOT allow records where ownerID1, ownerID2, accountKey and argID1 are the same.

Can anyone help me with this at all?

Thank-you!

*It usually stops after writing 2 records, so something is obviously believing its duplicated.* - Why is that obvious? What error message do you get when the insert fails? — Mark Byers, Jan 05 '11 at 13:12
You didn't specify `NOT NULL` on any columns, so maybe you're getting null values which violate the unique constraint. What does the data look like after an insert fails? — Hollister, Jan 05 '11 at 13:39

score 2 · Accepted Answer · answered Jan 05 '11 at 13:39

I'm not sure what is the problem. It works fine 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))

The error I get from last line is:

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

score 0 · Answer 2 · answered Jul 04 '12 at 23:48

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

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

Multiple Unique Columns in SQLite

2 Answers2

Linked