SQLite中的多个唯一列

Question

我试图创建一个表，我需要它不允许3个字段相同的行。

当我使用SQLLite在Python中创建表时，使用了以下代码，但几乎没有得到任何结果。它通常在写入2条记录后停止，因此显然可以相信它是重复的。

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)
);

因此，我希望数据库不允许在ownerID1，ownerID2，accountKey和argID1相同的记录。

谁能帮我这个忙吗？

谢谢！

Answer 1

我不确定是什么问题。它在这里工作正常：

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))

我从最后一行得到的错误是：

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