I'm trying to make sure when I run the following query only the first INSERT INTO
will work.. I know I have to make slot
UNIQUE
我试图确保当我运行下面的查询时,只有第一次插入才会生效。我知道我必须使插槽独一无二
The slot could be from 0-5 INTEGER but it doesn't mean that only 6 table data rows could be accepted into that table.
槽可以是0-5的整数,但这并不意味着只能接受6个表数据行到该表中。
For each playerHash that matches it should only allow 6 table data rows as slot
is UNIQUE
(cannot have duplicate of same slot column, for each playerHash column).
对于每个匹配的playerHash,它应该只允许6个表数据行,因为slot是惟一的(每个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);
Problem of course is they all pass and cause duplicate entries
当然,问题是它们都通过并导致重复的条目
Currently I use this table DDL
目前我使用这个表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
);
1 个解决方案
#1
25
Add to your ddl
添加到您的ddl
create table ... ( ...
...,
unique(slot, player));
#1
25
Add to your ddl
添加到您的ddl
create table ... ( ...
...,
unique(slot, player));