SQLite 中的多个唯一列 [英] Multiple Unique Columns in SQLite

问题描述

我正在尝试创建一个我需要它的表，不允许有 3 个字段相同的行.

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

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

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

所以，我希望数据库不允许 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?

谢谢！

推荐答案

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

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

这篇关于SQLite 中的多个唯一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！