SQLite：防止重复行 [英] SQLite: Preventing Duplicate Rows

问题描述

CREATE TABLE Permission ( 
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER
    location     INTEGER 
);

我不想让用户或位置 UNIQUE ，因为我可以有多个行用户包含相同的数据，或多个行与位置包含相同的数据。我只想避免让用户和位置都具有某个值，该行重复任何次数。

I don't want to have user or location to be UNIQUE because I can have multiple rows with user containing the same data, or multiple rows with location containing the same data. I just want to avoid having both user and location having some value, with that row repeating any number of times.

例如：这是可以

permissionID user location
--------------------------
      1        1     2
      2        2     2
      3        2     1

但这不行：

permissionID user location
--------------------------
      1        1     2
      2        1     2

如何避免重复？

推荐答案

声明对（用户，位置）的唯一约束。

Declare a unique constraint on (user, location).

CREATE TABLE Permission (
    permissionID integer primary key,
    user integer not null,
    location integer not null,
    unique (user, location)
);

sqlite> insert into Permission (user, location) values (1, 2);
sqlite> insert into Permission (user, location) values (1, 2);
Error: UNIQUE constraint failed: Permission.user, Permission.location

这篇关于SQLite：防止重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！