SQLite唯一键与两列的组合 [英] SQLite Unique Key with a combination of two columns

问题描述

我想确保当我运行以下查询只有第一个 INSERT INTO 将工作..我知道我必须使 UNIQUE

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

插槽可以是0-5 INTEGER，

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.

对于与之匹配的每个playerHash，只允许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).

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

目前我使用这个表DDL

Currently I use this table 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 
);

推荐答案

添加到您的ddl

create table ... ( ...
...,
unique(slot, player));

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