SQLite重建挪威语言银行词库时非唯一主键错误求解

问题背景 #

我尝试依据挪威语言银行的表结构，在SQLite中重建词库数据库。先创建了BOYING表：

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL,
  "BOY_NUMMER" integer NOT NULL PRIMARY KEY,
  "BOY_GRUPPE" varchar(32),
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32)
);

随后插入简化后的数据：

INSERT INTO BOYING (LOEPENR,BOY_NUMMER,BOY_GRUPPE,BOY_TEKST,ORDBOK_TEKST) VALUES
(1,1,'substantiv','ent ub','entall_ubestemt'),
(2,2,'substantiv','ent be','entall_bestemt'),
(3,3,'substantiv','fl ub','flertall_ubestemt'),
(4,4,'substantiv','fl be','flertall_bestemt'),
(5,1,'verb, normal','inf','infinitiv');

由于第五条数据的BOY_NUMMER与第一条重复，触发UNIQUE constraint failed: BOYING.BOY_NUMMER错误。主键必须唯一，想知道挪威语言银行是如何构建该数据库的，以及该如何解决此问题？

解决方案 #

1. 原表主键逻辑解析 #

挪威语言银行的BOYING表中，BOY_NUMMER并非单独作为主键，而是复合主键——主键由BOY_GRUPPE（词类组）和BOY_NUMMER共同组成。同一BOY_NUMMER可以对应不同的词类组，比如名词组的1号屈折类型和动词组的1号屈折类型是完全独立的条目。

2. 修正SQLite表结构 #

将单主键改为复合主键，修改建表语句如下：

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL,
  "BOY_NUMMER" integer NOT NULL,
  "BOY_GRUPPE" varchar(32) NOT NULL,
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32),
  PRIMARY KEY ("BOY_GRUPPE", "BOY_NUMMER")
);

修改后，BOY_GRUPPE与BOY_NUMMER的组合唯一，就能正常插入重复BOY_NUMMER但不同BOY_GRUPPE的数据。

3. 可选优化方案 #

如果需要保留LOEPENR作为全局唯一的自增标识，可以将其设为主键，同时给BOY_GRUPPE+BOY_NUMMER添加唯一约束：

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "BOY_NUMMER" integer NOT NULL,
  "BOY_GRUPPE" varchar(32) NOT NULL,
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32),
  UNIQUE ("BOY_GRUPPE", "BOY_NUMMER")
);

该设计既保证了每条数据有独立的自增ID，又符合原数据库中屈折类型按词类分组的逻辑。

内容的提问来源于stack exchange，提问作者Tambarskjelve