列中的唯一值，但仅对每个关联外键唯一。

Question

我为这个令人困惑的标题道歉，但我真的不知道如何用一句话来描述这个问题。

我有一个SQLite数据库，用于一个通用的小费竞争。对于那些不熟悉这个概念的人来说，他们认为他们认为在每一轮体育比赛的每一轮比赛中都会赢的人是“小费”，而在比赛结束时得到最正确提示的人则会获胜(奖品可能是金钱、骄傲等)。它在澳大利亚非常受AFL的欢迎。

其基本结构是用户注册为所有者，然后他们可以创建任意数量的竞争，每个竞争都是用外键连接到他们身上的。这是数据库模式的相关部分：

create table owner (
    owner_id INTEGER NOT NULL,
    owner_name VARCHAR NOT NULL,
    owner_nick VARCHAR UNIQUE NOT NULL,
    owner_email VARCHAR NOT NULL,
    owner_password VARCHAR NOT NULL,
    CONSTRAINT owner_pk PRIMARY KEY (owner_id)
);

create table comp (
    comp_id INTEGER NOT NULL,
    comp_owner_id INTEGER NOT NULL,
    comp_name VARCHAR UNIQUE NOT NULL,
    CONSTRAINT comp_pk PRIMARY KEY (comp_id),
    CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE
);

问题的要点是:我希望对comp表的comp_name字段强制执行唯一的约束(如上面所示)，因为单个所有者不应该有两个或多个同名的竞争。然而，两个不同的所有者完全有可能想要两种同名的不同比赛(例如，两个办公室为同一个体育联盟举办小费比赛)。是否可以使用SQL强制执行此操作，还是需要在其他地方手动执行？

几个注意事项：

• 我使用的是SQLite 3.6.20，它具有适当的外键约束强制执行
• 数据库将永远不会在生产级应用程序中使用。这是个私人项目。
• 该数据库正在基于web的PHP应用程序中使用。
• 如果不能用SQLite实现这一点，那么可以用MySQL实现吗？

编辑:我觉得我应该指出，我知道SQLite对alter的支持非常有限，删除数据库并重新开始是一个很好的选择。

Answer 1

只需在这两个字段上创建一个UNIQUE约束，如下所示：

create table comp (
    comp_id INTEGER NOT NULL,
    comp_owner_id INTEGER NOT NULL,
    comp_name VARCHAR NOT NULL,
    CONSTRAINT comp_unq UNIQUE (comp_owner_id, comp_name),
    CONSTRAINT comp_pk PRIMARY KEY (comp_id),
    CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE
);

做你想做的事。

Answer 2

可以使用以下语法创建复合唯一约束：

create table comp (
    comp_id INTEGER NOT NULL,
    comp_owner_id INTEGER NOT NULL,
    comp_name VARCHAR,
    CONSTRAINT comp_pk PRIMARY KEY (comp_id),
    CONSTRAINT comp_unique UNIQUE (comp_owner_id, comp_name),
    CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE
);

如果您的关注点是将约束添加到已经存在的表中，则可以使用以下方法进行：

 CREATE UNIQUE INDEX comp_unique (comp_owner_id, comp_name) ON comp

另外，您可以考虑丢失owner_id列，只需使用owner_name作为所有者表中的主键。类似地，您可能不需要comp_id，因为该表中有另一个候选键。