我想确保当运行以下查询时,只有第一个INSERT INTO
会生效.. 我知道我必须将slot
设为UNIQUE
slot
可以是0-5的整数,但这并不意味着该表格只能接受6个数据行。
对于每个匹配的playerHash
,它应仅允许6个表格数据行,因为slot
是UNIQUE
(对于每个playerHash
列,不能具有相同的slot
列的重复项)。
//Below Query Should Pass
INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 1, 1);
//Below Query Should Fail
INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 1, 1);
//Below Query Should Pass
INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 1, 2);
//Below Query Should Fail
INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 1, 2);
//Below Query Should Pass
INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 0, 2);
问题在于它们都通过并导致重复条目。
目前我使用的是这个表的DDL。
CREATE TABLE Buying (
id INTEGER PRIMARY KEY AUTOINCREMENT,
itemId INTEGER NOT NULL,
amount INTEGER NOT NULL,
price INTEGER NOT NULL,
bought INTEGER NOT NULL,
collected INTEGER NOT NULL
DEFAULT ( 0 ),
overpaid INTEGER NOT NULL
DEFAULT ( 0 ),
slot INTEGER NOT NULL,
aborted BOOLEAN NOT NULL
DEFAULT ( 0 ),
playerHash INTEGER NOT NULL
);